Power BI Userelationship function with example
There are a few relationship functions in DAX that are extremely helpful, and one of those functions is USERELATIONSHIP. This function is utterly useful to link your calculations or functions with inactive relationship columns. This function does not return any value but serves as a powerful tool when combined with functions that have filters as arguments, such as Calculate function. One Power BI USERELATIONSHIP function defines one relationship; hence, to use multiple relationships, more than one function will be required.
Power BI userelationship Syntax
The syntax of this valuable function is straightforward and has two parameters:
USERELATIONSHIP(<column1>, <column2>)
Where column1 and column 2 are the names of the column used for calculations. Column1 represents the lookup key or one side of the relationship, whereas column2 represents the many sides. The syntax is simple, yet the concept can be difficult to grasp in the beginning. Do not fret at the example below will help explain the function in great detail.
Userelationship example
Suppose you have conducted an experiment and taken two samples from the same individual on two different dates; the first test date and the second test date.
Considering that the first test date has an active relationship, as shown below, the score that we retrieve from the CALCULATE function will be based on the first test date. But how do you retrieve the scores based on the second test date? This is where the USERELATIONSHIP function comes into play. Second test date has an inactive relationship, so here is how you can use USERELATIONSHIP function to attain your desired results:
Using the score column, we get the sum of scores based on the first test date as a measure.
Now lets us get the same total score by the second date and compare the scores, side by side:
Total Score by Second Date = CALCULATE([TotalScores], USERELATIONSHIP (‘Test Details'[Second Test Date], DimDate [Date]))
In the expression above, you can see how the Power BI USERELATIONSHIP function was used with the second date and required the CALCULATE function to get the test scores based on the second test date. Now that you have the hang of this valuable function, you can use it in your daily dashboards and become the power user you wish to be.
Summary
Some other great functions that you can use this function with are but not limited to:
- CALCULATE
- CALCULATETABLE
- CLOSINGBALANCEYEAR
- TOTALMTD
- TOTALQTD
- TOTALYTD
This function is mostly used for inactive relationships because the relationship’s status is not crucial as the user will just override any default active relationship with the mentioned argument. You can nest this function up to 10 times, but be careful not to overcomplicate your expressions as it can affect the readability of your queries. It is not possible to use USERLATIONSHIP function with RELATEDTABLE in the calculated column, but you can certainly work your way around this by using CALCULATETABLE. You can refer to the official documentation to see more examples of the usage, but this brief document with the example has covered most of what you need to know about the function.
For more useful blogs, please visit Learn DAX
