Power Bi Relationship Functions

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.

Function	Description	Syntax
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>)

Read Power Bi Time Intelligence Function

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:

Read Power Bi Text Functions with Examples

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))

Read Power BI Information Functions

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.

Scenario

• 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])

Read Power Bi count function – DAX Examples

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.

Scenario

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'))

Read Power BI average function with Examples

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.

Scenario

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.

Active relationship

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]))

Related Posts:

• 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