USERRELATIONSHIP function is a Power BI Relationship function in DAX which Specifies the relationship to be used in a specific calculation as the one that exists between two columns.
To use USERRELATIONSHIP function there must be an existing relationship between tables as it activates only an existing relationship between tables.
It enables the indicated relationship for the duration of the calculation.
SYNTAX USERELATIONSHIP(<column_name1>,<column_name2>)
column_name1 is the name of an existing column, it usually represents the many side of the relationship to be used. If the arguments are given in reverse order the function will swap them before using them.
column_name2 is the name of an existing column, It usually represents the one side or lookup side of the relationship to be used. If the arguments are given in reverse order the function will swap them before using them.
Lets look at an example of USERRELATIONSHIP function.
Power BI does not support more than one active relationship between two tables in data model, so when two tables are linked by more than one relationship, obviously one relationship will be active all the time while another relationship will be inactive.
So for any DAX calculation if you want to use inactive relationship then you can active that inactive relationship using USERRELATIONSHIP function and the relationship will be active for the duration of the calculation.
As you can see in below screenshot there are two tables named CalendarTable and CustomerOrderDetails in data model, and there is one active relationship between these two tables based on OrderDate and Date columns.
If you create a second relationship between tables based on ReceivedDate and Date column, the second relationship is considered as an inactive relationship as shown below.
Suppose, you want to see the Order’s Total amount by Year then the Orders whose OrderDate belongs to the filtered Year are displayed in report. Data will not be filtered based on ReceivedDate although tables having a relationship based on column ReceivedDate but that relationship is inactive.
Lets see the data in matrix visual, as you can see the total amount is displayed for those orders whose OrderDate belongs to the filtered year.
For calculating TotalAmount following DAX is used.
Total Amount = SUM(CustomerOrderDetails[Amount])
If you want to filter the Order’s data based for ReceivedDate, you have to activate the relationship for ReceivedDate using USERRELATIONSHIP function.
Following DAXÂ [Amount_By ReceivedDate] uses a USERRELATIONSHIP function which activates the relationship based on column ReceviedDate between tables CalendarTable and CustomerOrderDetails and returns the total amount returned by measure [Total Amount].
Amount_By ReceivedDate = CALCULATE ( [Total Amount], USERELATIONSHIP (CustomerOrderDetails[ReceivedDate], CalendarTable[Date] ) )
Lets drag the measure Amount_By ReceivedDate into matrix visual.
You can see, In Amount_By ReceviedDate column order data is filtered based on ReceivedDate for year. Now you can easily see the Order Data based on OrderDate and ReceivedDate.
USERRELATIONSHIP function is very useful when feel need to use multiple relationship between tables then you can enable any relationship temporarliy during you DAX calulation.
Also Read..
1,911 total views, 2 views today