Power BI Userelationship

What is the Power BI Userelationship function?

The Power BI Userelationship is a relationship function that allows you to specify a relationship between two columns that can be used in a specific calculation for your reporting purpose. This function can be combined with other data manipulation functions to perform complex calculations. 

The Power BI Userelationship function doesn’t provide any output; however, it provides you the flexibility to indicate a relationship between the two columns to be used in the context of the calculation.

Syntax

The syntax for the Power BI Userelationship function in DAX expression is outlined below:

USERELATIONSHIP(<columnName1>,<columnName2>)

It accepts 2 parameters that include:

Parameter Name	Mandatory/Optional	Description
ColumnName1	Mandatory	This parameter represents the existing column of a table or dataset using which the relationship can be established.
ColumnName2	Mandatory	Similar to ColumnName1, this parameter also represents the existing column using which the relationship can be established.

For example, the below DAX calculates the total gross sales of Nike by establishing a relationship between OrderDate and DateTime table using the following expression:

= CALCULATE(SUM(Nike_Sales[GrossSales]), USERELATIONSHIP(Nike_Sales[OrderDate], ‘Calendar'[Date]))

In the next section, we will see how to use the Power BI Userelationship function with step-by-step instructions.

How to use the Power BI Userelationship function?

To use the Power BI Userelationship function using DAX expression, follow the steps highlighted below:

Step 1: Launch Power BI Desktop in your system.

Step 2: Import the dataset into Power BI using the Get data option and load it into Power BI using the Load button.

Step 3: Create a Calendar table by navigating to the Modeling – New table. Use the DAX function such as the CALENDARAUTO function to generate the Calendar dataset.

Step 4: Navigate to the Model view tab and establish a relationship between the columns of your dataset and the newly created Calendar table date column.

Step 5: Navigate to the Modeling tab and click on the New measure option.

Step 6: In the formula tab, write the DAX expression to create a calculated measure using the Power BI USERELATIONSHIP function.

Userelationship Measure = CALCULATE(SUM(financials[Gross Sales]),USERELATIONSHIP(financials[Date],’Calendar'[Date]))

Step 7: Navigate to the Visualizations pane and choose a visual icon. Map the data fields from the Data pane, including the created measure, to the visualization for your Power BI reporting.

Note: You can’t directly create the Power BI Userelationship calculated column, unlike the calculated measures. However, as a workaround, you can use LOOKUPVALUE in combination with the RELATED function in Power BI or create a calculated measure using the Userelationship function and then use that measure in your calculated column.

Keep in mind that you can’t use Power BI Userelationship with row level security with functions like Power BI CALCULATE or CALCULATETABLE if the underlying table has row-level security defined. 

Examples

In this section, we will go through a few examples demonstrating the use of the Power BI Userelationship function.

Example #1 

In this example, we will demonstrate the creation of the Power BI Userelationship calculated measure using the Invoice Orders dataset. The Invoice Orders dataset contains data about the invoices generated for orders placed at the supermarket, including order values and whether or not converted sales were made from them. 

To create the Power BI Userelationship calculated measure, follow the instructions below:

Step 1: Import the dataset into Power BI using the Get data option and click on the Load button to load it into Power BI.

Step 2: Create a Calendar table by navigating to the Modeling tab and clicking on the New table option.

We have used the CALENDARAUTO function to generate the Calendar table.

Step 3: Navigate to Model view to establish a relationship between Invoice Orders and Calendar Table.

Here we have created relationships between the Order Date and Calendar Date and Date of Meal and Calendar Date.

Step 4: Navigate to the Modeling tab and select the New measure option.

Step 5: Write the DAX expression using USERELATIONSHIP to create the calculated measure in the formula tab.

Total Value of Meal Served = CALCULATE(SUM(‘Invoice Orders'[Meal Price]),USERELATIONSHIP(‘Invoice Orders'[Date of Meal],’Calendar'[Date]))

Click on the Commit icon to save the changes.

Step 6: Navigate to the Visualizations pane and choose the Table visual. Drag and drop the fields including the newly created measure to the visual.

It will create a Table visual in the report canvas, as shown below.

Example #2

In this example, we will demonstrate the Power BI Userelationship function using the Sales Pipeline dataset. The Sales Pipeline dataset contains detailed insights into the process and performance of sales opportunities, offering insights into how successful each opportunity has been. 

To use the Power BI Userelationship function, follow the instructions below:

Step 1: Import the dataset into Power BI using the Get data option and click on the Load button to load it into Power BI.

Step 2: Navigate to Model view to establish a relationship between Sales Pipeline and Calendar Table.

Here we have created relationships between the Created Date and Calendar Date and Closed Date and Calendar Date.

Step 3: Navigate to the Modeling tab and select the New measure option.

Step 4: In the formula bar, write the DAX expression using the USERELATIONSHIP function to create the calculated measure.

Here, we have created a calculated measure that captures the total sales where the sales team lost the sales deal, i.e., they couldn’t close a deal.

Total Sales Lost = CALCULATE(SUM(‘Sales Pipeline'[Sales]), USERELATIONSHIP(‘Sales Pipeline'[Closed Date],’Calendar'[Date]), ‘Sales Pipeline'[Stage] = “Closed Lost”)

Click on the Commit icon to save the changes.

Step 5: Navigate to the Visualizations pane and choose the Clustered column chart visual. Drag and drop the fields, including the newly created measure.

Step 6: Apply formatting options to the visual as needed.

It will create a Clustered column chart visual in the report canvas, as shown below.

Example #3

This example is focused on demonstrating the Power BI Userelationship with filter option using the Customer Transaction dataset. The Customer Transaction dataset contains details of customer transactions with Demographic and Shopping Behavior Information. 

The step-by-step instructions to use the Power BI Userelationship with filter is highlighted below:

Step 1: Choose the dataset using the Get data option. Load the dataset into the Power BI data model by clicking on the Load button.

Step 2: Now establish a data relationship between Customer Transaction and Calendar Table by navigating to Model view and mapping the linkage using the Date columns.

As you can see below, the relationship between Calendar and Customer Transaction tables has been set up using:

• Transaction Date and Calendar Date
• Birthdate and Calendar Date

Step 3: Create a calculated measure by navigating to the Modeling tab and selecting the New measure option.

Step 4: Specify the logic by writing the DAX expression using the USERELATIONSHIP function in the formula bar.

Total Transaction Amount by Females on Travel Segment = CALCULATE(SUM(‘Customer Transaction'[Transaction Amount]), USERELATIONSHIP(‘Customer Transaction'[Transaction Date],’Calendar'[Date]),

‘Customer Transaction'[Gender] = “F”, ‘Customer Transaction'[Purchase Segment] = “Travel”)

Click on the Commit icon to save the changes.

Step 5: Navigate to the Visualizations pane. Choose the Clustered column chart visual icon. Drag and drop the data fields, including the newly created measure, from the Data pane to the X-axis and Y-axis.

Step 6: Customize the visual by applying further formatting options such as Title, Text, and colors to your visual as needed.

You will see a Clustered column chart visual in the report canvas, as shown below:

Important Things to Note

• Power BI Userelationship function can be used in only in those functions that accept filter as argument such as CALCULATE, CALCULATETABLE, OPENINGBALANCEYEAR, TOTALMTD, TOTALQTD, and TOTALYTD etc.
• Power BI will throw an error if you include a measure using the Power BI Userelationship with row level security (RLS) defined for the underlying table.
• Power BI Userelationship will throw an error if the columns specified in the input parameters are not part of any relationship or are part of different relationships.
• Always consider indicating each relationship in a different Power BI Userelationship where you need multiple relationships to join two different tables in a calculation.
• For nested CALCULATE expressions containing individual Power BI Userelationship functions, the innermost function takes precedence over the others in case of a tie or any conflicts.
• There is a limitation that you can only specify up to 10 Power BI Userelationship that can be used in nested calculations.
• Where you have defined a one-to-one relationship, Power BI Userelationship activates only the unidirectional relationship. 

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Power BI Userelationship. Here we explain how to use it in Power BI, with syntax, examples, benefits, and points to remember. You may learn more from the following articles –

• Relationships in Power BI
• Power BI Natural Language Query
• Power BI Real Time Streaming