DAX USERELATIONSHIP Function

This DAX function is used to specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.

In simple words, it help us to make a relationship between inactive relationship columns. It comes under Relationship functions DAX category.

Syntax

USERELATIONSHIP(<columnName1>,<columnName2>)

Description

columnName1: A fully qualified name of a column that represents the many side of the relationship to be used.

columnName2: A fully qualified name of a column that represents one side or lookup side of the relationship to be used.

Download sample dataset:

Download sample Dataset – SuperStoreUS-2015.xlxs and import into Power BI desktop.

Understand the Requirement

How you will count month wise orders & ship orders, same like below screenshot. The challenge is here, which date column you will use to display month & calculation for counts?

• If you used Order Date then it will return correct count for Orders, not for Ship.
• If you used Ship Date then it will return correct count for orders shipped, not for Orders.

Understand the Relationship

In order to solve this situation we created a calendar table and after that we made a relationship between both dates(Order date & Ship date) with Calendar Date.

You can see the relationship in below screen shot but here you can active one relationship at a time. You can see relationship with solid line(Order date to Calendar date) is Active and dotted line relationship(Ship date to Calendar date) is an Inactive.

That’s why USERELATIONSHIP DAX comes on role and it will help us to make an Inactive relationship column to Active.

Let’s get started-

Step-1: Load Orders dataset into Power BI.

Step-2: Create a calendar table using CALENDARAUTO Dax function.

Go to modeling tab > click on table and write below DAX-

Calendar = CALENDARAUTO()

Step-3: Now create a relationship between Order date & Ship date to Calendar Date.

Click on Model Tab > Drag Order date to Calendar Date > Drag Ship Date to Calendar Date

Step-4: Now create a measure to count the number of orders shipped, here no need to create a measure for Orders count because Order date relationship is Active with Calendar date.

Count of Ship =
CALCULATE (
COUNT ( Orders[Ship Date] ),
USERELATIONSHIP ( Orders[Ship Date], 'Calendar'[Date] )

)

Step-5: Now add one Matrix visual in Power BI report page and drag some fields with measure.

Rows: Drag Calendar dataset Month column.

Values: Drag Order Date then right click on Order date and select count. And drag “count of ship” measure.

So, you can saw above screenshot how we active the “Ship date” column relationship with calendar date and got the result.

Note:

• This function is used to identifying relationships by their ending point columns.
• we can join Table A and Table B in a calculation, each relationship must be indicated in a different by using this function.
• This function can’t be used when row-level security is defined for the table in which the measure is included. It can only be used in DAX functions that take a filter as a parameter. For example, CALCULATE, CALCULATETABLE, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, TOTALMTD, TOTALQTD and TOTALYTD functions.
• Up to 10 nested function can be used with USERELATIONSHIP functions.

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.