Power BI DAX’s USERELATIONSHIP Function
The secret sauce for Role-playing dimension data models in Power BI
Hello, and welcome to my blog post! I’m Parth Lad, a data analyst who loves working with Power BI. In this blog, I will share with you some of the tips and tricks that I have learned over the years.
Today I’m going to show you how to use one of the most powerful and versatile functions in DAX: USERELATIONSHIP. This function allows you to activate or deactivate a relationship between two tables in a calculation, giving you more control and flexibility over your data model. Sounds great, isn’t it? Let’s see how it works!
First, you need to have at least two tables with a relationship between them. For example, let’s say you have a Sales table and a Date table, and they are related by the OrderDateKey and DateKey columns. By default, this relationship is active, meaning that any calculation that involves both tables will respect the filter context based on the Order Date.
If you are not interested in the current filter context and you want to activate another inactive relationship. Perhaps, you are interested in the sales amount based on the shipping date, regardless of the selected date? This is where USERELATIONSHIP comes in handy. You can use it to deactivate the active relationship between the tables and create a new filter context based on another column (In this case, ShipDateKey).
Here’s how you can write a measure that calculates the sales by shipping date using USERELATIONSHIP:
Sales Amount by Shipping Date =
CALCULATE (
SUM ( FactInternetSales[SalesAmount] ),
USERELATIONSHIP ( FactInternetSales[ShipDateKey], DimDate[DateKey] )
)This measure will sum up the sales amount by shipping date, using the ShipDateKey column as the filter context instead of the OrderDateKey column. Meanwhile, the Sales Amount by Order Date can be calculated by simply aggregating the amount since the relationship is active by default. We can see the difference in sales amount by month and total in the picture below. From this exercise, we can analyze that some orders which were placed in January were not shipped within the same month.
The USERELATIONSHIP function takes two arguments: the columns that define the relationship you want to activate. In this case, we are deactivating the relationship based on Order Date and activating the one based on Ship Date.
As you can see, USERELATIONSHIP is a very useful function that can help you create more complex and dynamic calculations for Role-playing dimensions in Power BI. In the next post, I will show you how we can combine USERELATIONSHIP and Calculation Groups to activate or deactivate multiple relationships dynamically for all available measures in the Power BI data model using the SELECTEDMEASURE function.
This article was published in Microsoft Power BI publication, don’t forget to sign it for more interesting Power BI Tutorials, Tips and Tricks.
If you’re looking to level up your DAX skills, check out my blog post on other relationship functions! CROSSFILTER and RELATED & RELATEDTABLE
If you enjoyed this blog post and learned something useful and would like to receive notifications about my new blogs, please follow and subscribe to my Medium account. Also, please don’t forget to give me a clap and save it on your reading list. Stay tuned for my next blog posts with tons of other Power BI tips and tricks. Feel free to leave your comments and feedback below. Thank you for visiting!
