Introduction
Power BI is a business intelligence and data visualization tool used for ;
- Visualizing data
- Clean and transform messy data
- Creating interactive dashboards and reports
- connecting databases and datasets
- Performing data analysis
- Share insights
- Create relationships
- Analyzing large amounts of data
- Making data-driven decisions
Power BI also helps users move from raw data to meaningful business insights.
Power BI Dax
Data Analysis Expression(DAX) is a library of functions and operators that can be combined to build formulas and expressions in power BI.
Common DAX functions includes aggregate functions which is divided into two;
Simple aggregation - This is used when finding either the Sum,MIN,MAX,Median,Average,Count and DistictCount.

Iterator Functions - These are functions that works in row by row and ends in an X. (For example SumX or Average X)
What are the use cases of the above mentioned functions?
Median aggregate
This is used when finding the middle value of a sorted dataset in either ascending or descending order.MIN & MAX aggregate
These are used in finding the smallest and largest values in a dataset.Count aggregate
This is used to count non-blank and blank values in a column.Absolute function
This gives you the absolute value of a number ,column or a measure. It's commonly used in calculated columns or measures to avoid negative signs for magnitude-based measures.Power function
The power function raises a number to a given power.Square root function(sqrt)
The sqrt function finds the square root of a number.MOD function(modulus)
It is used to return the remainder of a number after division.Text function
It is used to clean data and create readable texts.
There are different types of the text function that includes;
- - Concatenator( & operator)- It's used to join two columns.
- - The Left function - Extracts the left-most character of a function.
- - The Right function - Extracts the right-most character of a function.
- - The mid function(middle)- Used to extract the mid-most characters of a functions.
- Length & Trim function This function is used to find the length of a text and trim function is used to remove extra spaces in between texts.
Power BI Joins
Power bi joins are used to combine data from multiple tables using common keys, primarily achieved through Merge Queries in Power Query Editor which physically flattens tables or Relationships in the Data Model View
- A Power Query Editor is where you clean, transform, merge, and prepare data before loading it into Power BI.
Before creating Relationships(joins) in Power Bi, it is important to understand about Primary and Foreign Keys as they determine how tables are connected.
A primary Key is a column that uniquely identifies each row in a table and it should not have duplicates or be left blank. Each table should ideally have only one primary key.
A Foreign Key is a column in one table that refers to a Primary Key in another table and foreign keys are used to connect tables.
Primary Keys and Foreign Keys helps Power BI understand how tables relate to each other. Additionally, without proper keys, Power BI cannot correctly combine information from different tables.
The difference between Joins and Relationships is that; a relationship connects tables in the Power BI model without physically combining them into one table while a Join combines tables in Power Query and creates a new merged query. Therefore, use relationships when you want a clean Power BI model and Joins when you want to physically combine columns or identify missing/unmatched records.
Different types of Joins
1.Inner Joins
This type of join only returns the rows that have matching values in both tables and it only keeps records that exists on both sides.
2.Full Outer Joins
This type of join returns all rows from both tables. It also keeps matching rows from both tables be it rows from the left with no match or rows from the right with no match. If there is no match, Power BI fills the missing side with null values.
A full outer Join says that show me everything from both tables whether it matches or not.
3.Left Outer Join
This type of join returns all rows from the left table and only matching rows from the right table. If a row in the left table has no matching row in the right table, it still appears.
The missing right-side values appears as null.
A left outer Join says keep everything from the first table, and bring matching information from the second table where available.
4.Right Outer Join
This type of join returns all rows from the right table and only matching rows from the left table. If a row in the right table has no matching row in the left table, it still appears.
The missing left-side values appear as null.
A right outer join says Keep everything from the second table, and bring matching information from the first table
where available.
5.Left Anti Join
This type of join returns only the rows that doesn't have a match from the left. It only shows what exists in the left table but not in the right table.
A left anti join says show me records from the first table that do not exist in the second table.
6.Right anti Join
This type of join returns only rows from the right table that do not have a match in the left table. It only shows what exists in the right table but not in the left table.
A right anti join says show me records from the second table that do not exist in the first table.
Power BI Schemas and Data Modelling
Data Modelling in Power BI is the process of organizing, structuring and defining relationships between different data tables so that they can be analyzed together.
A *schema * is a way tables are organized in a database or a reporting model.
There are two main types of schemas in data modelling;
- A Star Schema - It is a data model where there is only one central fact table.
Advantages of a Star Schema
- It works well with Power BI
- It reduces data redundancy
- It's easy to understand
- It makes DAX calculations easier
- It improves report performance
- It's easy to build relationships
Disadvantages of a Star Schema
- If relationships are wrong, records gives wrong results.
- Sometimes there are many tables to manage.
- It requires a better understand of relationships.
- One may be required to clean unmatched records before analysis.
2.A Snowflake Schema is a more normalized version of a star schema.
Advantages of a Snowflake schema
- Improves data organization
- It reduces data redundancy
- It enforces cleaner data structure
- It improves data integrity and consistency
Disadvantages of a Snowflake schema
- It has more relationships
- It can make DAX expressions very difficult
- It has more tables
- It is harder to explain to a non-technical user
Relationships
A relationship connects two tables using matching columns. Additionally there are 4 different types of relationships that includes;
- One-to-many relationship
- many-to-one relationship
The above mentioned two types of relationships represents cardinality. Cardinality describes how rows in one table relates to rows in another table.
- One-to-one relationship
- many-to-many relationship
conclusion
Power BI is not simply a reporting tool; it is a complete business intelligence solution, that transforms raw data into information. By becoming fluent in DAX functions, a business can unlock sophisticated calculations and formulate specific metrics to assist in better decision-making processes.
Mastering how joins and relationships operate means accurately bringing data together from various tables, while opting for an optimal data model like the star schema or snowflake schema streamlines report execution, upkeep, and precision.



Top comments (1)
Excellent article! Thanks for breaking down these concepts so well. 🙌