Power BI: DAX Function USERELATIONSHIP Explained

In the Power BI Desktop, you are allowed only 1 active relationship between tables in your data model.  Many times this is all that is needed.  There are, however, scenarios where this becomes a huge reporting issue.  For example, what if you have a table of sales where one column contains the date a sale occurs and another column that records the date the money from the sale is collected.  Well, depending upon which relationship is active with your Date table in your model the sum of your sales will either be based on the sale date or the money collected date.  If you need to see both values though this is where you need to make a separate measure using the function USERELATIONSHIP paired with the function CALCULATE.  The USERELATIONSHIP function will allow you to access inactive relationships in your data model. 

Another scenario, where I have seen this be an issue, is when users need to see their sales based on the city where the sale occurred compared to the city where the sales are being shipped.  Here again, we run into an issue of only being able to have one active relationship from our sales table to our Geography table.  If you have ever run into an issue like this check out this video explaining how to make inactive relationships and leverage the USERELATIONSHIP DAX function.

If you enjoy this video or any of my other videos and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Matt20" to get 20% off at check out when purchasing our On-Demand Learning classes. Learn more about our pricing.