I’m going to show you the difference between the Power BI USERELATIONSHIP function and the TREATAS function that are both used when creating DAX measures. You may watch the full video of this tutorial at the bottom of this blog.
I got this tutorial idea from a question that was raised on the Enterprise DNA forum. The member has a fact table that has a bunch of different dates in it and he was trying to create either an active or inactive relationship between that table and the Date table.
Here’s a little background on the Power BI USERELATIONSHIP and TREATAS functions. Let’s have a look at the definitions of these functions on the DAX guide through the Enterprise DNA website.
The Power BI TREATAS function applies a result of a table expression as filters to columns from an unrelated table. This means that there is no active or inactive relationship between, in this case, the Date table and the fact table.
On the other hand, the Power BI USERELATIONSHIP specifies a relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.
So, in this case, if we’re creating a DAX measure, USERELATIONSHIP function will be used if we had an inactive relationship between the Date table and our fact table.
Let’s go back to Power BI desktop and see how this all comes together.
Data Model Setup
Let’s have a look at my data model so you can see how this is all set up. It’s basically a very simple model. I have a fact table, which is called the Jobs, and then I have the Date table. And as you can see, I’ve already created measure tables for the TREATAS measures and for the USERELATIONSHIP measures.
In the data model, all these relationships with the dotted lines are inactive relationships between the Dates table and the Jobs table for multiple dates. This is when we would use the USERELATIONSHIP function.
If there are none of these inactive relationships between these two tables, that’s when we would use the TREATAS function.
When To Use TREATAS Function
Now let’s see how these measures look like for TREATAS when creating measures. In the fact table here, you can see that we have multiple dates. There’s a Received Date, Estimate Approved Date, Start Date, Invoice Date, and a Close Date.
Under TREATAS measures, we have the received count measure (Rec’d Count v2), which is just basically a CALCULATE formula to calculate the number of jobs. I use the TREATAS function and I’m using the VALUES function as well. And then, I just placed the Date column from the Date table and the Receive Date column from the Jobs.
Very similar situation if we look to get the Total Estimates number. As you see on the image below, I’m just calculating the total estimates with the TREATAS and I’m using that Date column from the Date table. And we’re using the Receive Date from the Jobs fact table as well.
When To Use The Power BI USERELATIONSHIP
Now let’s look at the Power BI USERELATIONSHIP is used in the measures. As you can see, the numbers on this page match the ones on the TREATAS page. So we’re using different functions to come up with the same answer to what we want to get for our DAX measure.
Remember that we have those dotted lines between the Date table and the fact table (Jobs) in the data model. So in this case, we have an inactive relationship and we can use the USERELATIONSHIP function. And what this function does is it actually makes that inactive relationship active.
So, if we look at the received measure (Rec’d Count) using the USERELATIONSHIP, you can see that it’s quite similar to the measure using TREATAS. Notice that I used VALUES in the TREATAS measure, but not in this measure with the USERELATIONSHIP function.
And then, here’s what that total estimate (Rec’d $$) measure looks like for the Received Date. Once again, we’re calculating total estimates, use the USERELATIONSHIP function, and then we’re using the Date column from the Date table and the Receive Date from the Jobs table.
Conclusion
In this tutorial, I showed you when to use the Power BI USERELATIONSHIP function as well as the TREATAS function. Notice that I used VALUES in the TREATAS measure.
Keep in mind when you run into an issue like this that you have different options. You can use TREATAS or USERELATIONSHIP depending on how your model is set up.
One thing you need to remember as well is that when you want to use TREATAS to create a DAX measure, you have to make sure that you don’t have any inactive or active relationships between the Date table and your fact table.
And vice-versa with USERELATIONSHIP in your DAX measure. You’re going to need an inactive relationship between the Date table and your fact table to create that measure.
To learn more about this topic, check out the links below and visit our website for more content.
All the best!
Jarrett
[youtube https://www.youtube.com/watch?v=SJhpZ9N3DVA?rel=0&w=784&h=441]
***** Related Links *****
Creating Virtual Relationships In Power BI Using The TREATAS Function
A Deep Dive Into The TREATAS Function In Power BI
Active And Inactive Data Relationships In Power BI
***** Related Course Modules *****
Mastering DAX Calculations
DAX Formula Deep Dives
Time Intelligence Calculations
***** Related Support Forum Posts *****
Comparing TREATAS vs USERELATIONSHIP in Power BI
Two Fact Tables for Same Dates Table
Userelationship Function
For more TREATAS USERELATIONSHIP queries to review see here…..
