In this Power Bi Tutorial, we will learn the different types of Power BI Relationship Functions with examples. And also we will discuss the below Points:
- What is Power Bi Relationship Function
- List Of Power Bi Relationship Functions
- Power Bi Relationship Function Examples
Power Bi Relationship Functions
The Power Bi Relationship Functions are used for managing and utilizing relationships between tables.
List Of Power Bi Relationship Functions
Here we will see a List of Relationship Function in Power Bi.
|CROSSFILTER||This Function specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two column||CROSSFILTER(<columnname1>, <columnname2>,<direction>)|
|RELATED||This Function returns a related value from another table||RELATED(<column>)|
|RELATEDTABLE||This Function evaluate a table expression in a context modified by the given filter||RELATEDTABLE(<table name>)|
|USERELATIONSHIP||This Function specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.||USERELATIONSHIP(<columnName1>,<columnName2>)|
Power Bi Relationship Function Example
Here we will discuss different types of relationship functions with the examples in Power Bi.
So here we will use the US Superstore dataset. And then in Power Bi, I build a dimensiontional data model using Power Query.
Then I created a relationship between the tables like below:
Example 1: Using Power Bi CROSSFILTER Function
Here we will see how to use the CROSSFILTER function in Power Bi.
The Power Bi CROSFILTER function is used to implement a specific cross-filtering behavior in a calculation where a relationship exists between two columns.
So, here we will calculate the total sales and distinct product count for each year by using measures.
First, we create a measure that will calculate the total sales in the sales fact table.
Total Sales = SUM('Fact-Sales'[Sales])
Then we will create a measure that will distinct count the product from the dim product table.
Distinct count on product = DISTINCTCOUNT('Dim-Product'[Product ID])
Now create a table visual, dim order table adds the year from the date hierarchy. Then add total sales and Distinct count on product measure in the table visual.
Now, in the above report, you can see the product count is repeated for each year. That means, even the relationship is mentioned between sales fact and product dimension, the filter is not applied as per the requirement.
So if we change the relationship between these two tables as both cross filter directions, then all other measures will be impacted.
So we will use the cross-filter direction relationship only for this measure. Let’s create a measure using CROSSFILTER Function to Distinct Product Count.
Product count on Crossfilter = CALCULATE(DISTINCTCOUNT('Dim-Product'[Product ID]), CROSSFILTER('Fact-Sales'[Product ID],'Dim-Product'[Product ID],Both))
Example 2: Using Power Bi RELATED Function
Here we will see how to use the RELATED Function in Power Bi.
The Power Bi RELATED Function returns a column from another related table
Key Points of the Related Function:
- Requires a relationship between the current table and the related table.
- This function follows many to one relationship. And without relationship it will not work.
- It will work only in row context.
- So here we will compare the yearly sales for all 4 regions as well as for the non-central regions.
- We will create the two measures, one will calculate the total sales, and in another, we will calculate the sales for the non-central region.
So total sales we have already calculated in Example 1.
So, let’s create a measure in which we will calculate the Non-central region total sales.
Noncentral region Total sales = SUMX(FILTER('Fact-Sales' ,RELATED('Dim-Geography'[Region])<>"Central"),[Total Sales])
Example 3: Using Power Bi RELATEDTABLE Function
Here we will see how to use the RELATEDTABLE Function in Power Bi.
The Power Bi RELATEDTABLE Function returns a table of values. It considered an existing table as a parameter.
Key Points of the RELATABLE Function
- RELATABLE Function is a shotcut function for the calculatetable function with no logical expression.
- This function follows one to many relationship.
So, here we will calculate how many times one product has been used in the sales table, which means we will do the product counts for the sales table.
For this we will create a calculated column in the product table, then we will find out the count of products in the sales table. CLick on Modelling tab -> New column.
Product count from sales table = COUNTROWS(RELATEDTABLE('Fact-Sales'))
Example 4: Using Power Bi USERRELATIONSHIP Function
Here we will see how to use the USERRELATIONSHIP Function in Power Bi.
The Power Bi USERRELATIONSHIP does not return anything but it specifies between two columns to define the relationship for calculations.
And also it uses the other functions as an argument like CALCULATE and CALCULATETABLE etc.
So, in our current data model, we have two date tables i.e. Dim Order Date and Dim Ship Date. And both the date table has one active join with a sales table using order date.
Now the question is how the ship date can be used if we want to calculate the total sales based on the ship date.
For this, we will create a new Date table, then we will add an active relationship between the order date in the sales table and the date column of the date table.
In Power Bi Desktop, Click on the Modelling Tab->New table, then write the below DAX formula to create Dim Date:
Dim Date = CALENDARAUTO()
Then we will create two relationships one is the active relationship i.e. order date column in Fact sales with the Date column in the new date table.
And the other relationship is the non-active relationship i.e. ship date column in Fact sales with the Date column in the new date table.
Non Active relationship
Now we will calculate the total order sales by using the below measure:
Total order sales = SUM('Fact-Sales'[Sales])
Then we will calculate the total sipped sales using the below measure:
Total Ship sales = CALCULATE(SUM('Fact-Sales'[Sales]),USERELATIONSHIP('Fact-Sales'[Ship Date],'Dim Date'[Date]))
- Power BI average function with Examples
- Power BI DAX Logical functions
- How to get selected value from Slicer in Power BI
- Power BI DAX SUM and SUMX function
- Power bi measure count with filter
- Power bi sort by measure
- Power Bi Filter Functions with Examples
In this Power Bi tutorial, we discussed different types of relationship functions in Power BI with examples. And also we discuss the below points:
- What is Power Bi Relationship Function
- List Of Power Bi Relationship Function
- Power Bi Relationship Function Example
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com