As soon as you start developing a few Power BI models, you’ll very likely run into a problem where you have two dates in your fact table and you can’t place two relationships between your date table and the other table. You may watch the full video of this tutorial at the bottom of this blog.
You might even have multiple dates, like entered date, ordered date, invoiced date, shipped date and potentially others.
You might think that there is a problem with Power BI, or that you need a brand new date table.
You don’t actually need another table, nor do you need to worry.
The key thing to realize is that you can have multiple relationships between tables. You can have lots of ‘inactive’ relationships but not more than one ‘active’ relationship.
The trick with inactive relationships is that you can turn them ‘on’ by using the right DAX formula.
In this post, I’m going to show you how to utilize the USERELATIONSHIP function together with the CALCULATE function to activate relationships in your data model.
Working With Dates in Power BI
If we take a look at our underlying table, you’ll notice that we have an Order Date column and a Ship Date column.

Then let us take a look at our data model and drag the date lookup table to our Order Date.

As you can see, we have a solid line here which represents an active relationship.

Let us then try to do the same thing but this time between the Date and the Ship Date.

What we have now is a dotted line which means that we have created what is called an inactive relationship.

As you can see, we can only build one active relationship.
Let us now see what will happen with the natural filtering with our active relationship.
We can create a new measure and call it Total Sales and then go SUM by the Total Revenue column.

If we drag this measure into the canvass and turn it into a table, you’ll see that we have the Total Sales column here.

What you need to take note, however, is that this is Total Sales by Order Date since this is the active relationship in our data model.
Aside from this, we need to make it clear what kind of date we are working on.
We can do this first by carefully naming our measures. For instance, instead of just saying Total Sales, we can call it Total Sales by Order Date.

As you can see, our table now says Total Sales by Order Date instead of just Total Sales.

But aside from specifying the date in our measure, we can also add a comment in our formula like this one below just to make it clear to whoever is using our data.

Now, how about if we want to show the Total Sales by Ship Date?
Remember that the Total Sales by Ship Date has an inactive relationship with the Dates Table. Let’s move on to how we can activate this relationship.
Turning on Inactive Relationship
To actually analyze Total Sales by Ship Date, we need to first turn on this inactive relationship because at its current state it does not register at all.

The good news is that this is actually very easy to do. We can use this special DAX function called USERELATIONSHIP to physically turn on one of those relationships.
So, we’re going to create a new measure and call it Total Sales by Ship Date.
Then we’re going to use CALCULATE and then Total Sales by Order Date and then jump down to another line.
Now, we’re going to add USERELATIONSHIP and then Dates then we’re going to add Sales by Ship Date.

If we drag this into the table here, you’ll see that the results in our Total Sales By Ship Date are very much different from those in our Total Sales by Order Date.

We were able to calculate our Total Sales by Ship Date because we have turned on this filter here and it has virtually become a solid line or an active relationship.

I’m sure you realize how easy it is to implement this technique but it might also be helpful for you to know that some people get confused when working with dates in Power BI, especially those who are still new.
Confusing Actions When Working With Multi Dates
One approach some people try is they go to the data model, click on the dotted line so that the Edit Relationship pop up box will appear.
Then they click on the “Make this relationship active” option.

If you try this approach, you’ll see that you will get an error like this one below.

And because the error message tells you so, you might try to deactivate the active relationship to turn on the other one but then you’re going to encounter another error.
So in case you are working on multiple dates in Power BI, all you need to do is utilize the USERELATIONSHIP function and then your calculations will be done easily.
Conclusion
We have just discussed how to work on a data set that has relationships and multiple dates in Power BI.
After utilizing the USERELATIONSHIP function together with the CALCULATE function we were able to activate relationships in the data model.
By using this technique, you’ll have the ability to quickly filter your data by any of these dates, and you open out a range of analysis that traditionally, with Excel, would have been very time-consuming to create.
Once you get your head around this technique, you can build some very effective Power BI data models.
Cheers,
Sam
***** Related Links *****
Show Results Up To Current Date Or A Specific Date In Power BI
Show Days Before Or After A Selected Date Using Power BI
The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI
***** Related Course Modules *****
Time Intelligence Calculations
Power BI Super Users Workshop
Advanced Data Transformations & Modeling
***** Related Support Forum Posts *****
Managing common metrics across multiple date tables
‘Role-Playing’ Multiple Date Dimensions to Index New Store Openings
Want Totals For Multiple Dates not connected to Dates Table
For more multiple dates support queries to review see here….
Great explanation. Thank you
Can you use “UseRelationship” without first creating an inactive relationship?
Can you use “UseRelationship” without first creating an inactive relationship?
No, you can’t. You need to create an inactive relationship first.
Would you consider using an alias of the Date dimension/table…? In power query, this is the reference feature. This could be simpler as does not require the use relationship function…
I always prefer just to have the one date table. It just makes the model cleaner.
What about performance problem? Userelationship function creates performance problems in models.
It depends on what model you have. From my experience performance is fine whenever I use this particular function.
Good to know! great explained
Could you elaborate more on the CALCULATE function that you have? Why does it need to bring in the expression “Total Sales by Order Dates”? Is it because that expression holds the main active relationship?
I’m creating something similar for a product, tracking various processing stages (10 of them) dates and wondering if i need to point all the measures to the same expression.
Hi Thomas – Thanks for your interest in Enterprise DNA Blogs.
“Total Sales by Order Date” is a simple measure that is calculating the Sum of Revenue from Sales Table. As explained in the Blog as Active Relationship between Date and Sales table is based on “Order date”.
When we use this measure in a visual along with Date Column, Power BI will filter Sales Table based on Order Date and evaluate the Revenue.
Calculate along with USERELATIONSHIP allows to use Inactive Relationship while doing calculation. Hence, in “”Total Sales by Ship Date”, Sales table will be filtered based on Ship Date while doing evaluation for Revenue.
For In-Depth knowledge on Calculate DAX function, Enroll into below EDNA course https://portal.enterprisedna.co/courses/108877
Please feel free to ask any further queries related to this Blog Post.
Thanks Bro….Finally i fix it…Thanks a lot….
Hello,
I have a problem with a table date.
I can calculate the inputs with the following formula : INPUT = CALCULATE([ST], USERELATIONSHIP(Calendario[Fecha],HISTORICO[Fecha_entrada_calculada]))
But when I try to calculate the stock (inputs minus outputs) with the following formula it doesn’t work for me:
STOCK = CALCULATE([ST],HISTORICO[Fecha_salida_calculada]>
Calendario[Fecha],HISTORICO[Fecha_entrada_calculada]<=Calendario[Fecha],)
Why?
Thanks verymuch
Hi Manuel,
Thanks for your interest in Enterprise DNA Blogs.
This issue will be due to no Relationship b/w Date and Fact table or there is a relationship b/w is an Inactive relationship.
Can get more information on USERELATIONSHIP DAX function at https://info.enterprisedna.co/dax-function-guide/userelationship/
If issue is still not resolved, then raise a request at EDNA Forum https://forum.enterprisedna.co/ and our team of experts will help you.