1 active relationship limitation between date table and fact table

Power BI
1

I have a fact table with (start date column, end date column, and I have set date table and set it as the date table. Given that, I needed to manually setup all relationships now but I ran into issue where PBI limits relationships to 1 active relationship. So I have setup an active relationship with the start date column, do I need to setup a relationship with the end date column? If so, how do I workaround the active relationships limitation?

Thanks in advance!

2

Hi @vvillela, welcome to the forum :slight_smile:

I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:
Here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Hello @vvillela,

Thank You for posting your query and we welcome you onto the EDNA Forum.

This is an perfect example of having multiple dates into the data model and how to manage them? Well, don’t worry about that we’ve got you covered here.

Firstly, since you’ve already created an active relationship between the “Start Date” and “Dates” table and Power BI also allows you to create and have only one active relationship at a time between the tables.

But you can create multiple in-active relationships in your data model as per your requirements. And now, using the “USERELATIONSHIP()” function you can activate the in-active relationships individually that has been created between the tables directly into the “Measure” itself.

And the best part is, you have to use this function in your base or primary measures only and then in subsequent measures you don’t have to make use of it again-and-again i.e. “Measure Branching Technique” will take care of it.

For example, let’s say you want to analyze the Sales based on End Dates. But End Dates is having an in-active relationship with the Dates table. So now, inside the measure itself you can activate this in-active relationship using the function. The measure will be like this -

Total Sales as per End Date = 
CALCULATE(
   SUMX(
      Sales , 
      Sales[Quantity] * Sales[Unit Price] ) , 
   USERELATIONSHIP( Sales[End Date] , Dates[Date] ) )

Now, once this measure is written and furthermore let’s say you want to analyze Sales Last Year based on End Dates then you don’t have to make use of this function again in your measures. The “Base Measure” or “Primary Measure” which we’ve created just above will take care of everything. Now, you just have to create the measure like you would do in normal circumstances. Below is the measure provided for the reference -

Total Sales Last Year as per End Date =
CALCULATE( [Total Sales as per End Date] , 
    DATEADD( Dates[Date] , -1 , YEAR ) )

You see that’s how much simple it is!!!

So now, I’m also providing the links of the articles, blog posts or videos pertaining to this very topic so that you can understand this concept in a much more bettter manner.

Hoping you find this useful and helps you to understand the concept as well as meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

https://curbal.com/blog/glossary/userelationship-dax

1 Like