To connect date with different datekey from same table

[conniedevina]

Hi, 

I am having this kind of fact_table

ID	category_a	category_b	categoryA_date	categoryB_date
12345	healthy	non-healthy	2024-01-01	2024-03-01
12345	sick	healthy	2024-05-01	2024-06-01
12344	very healthy	non-healthy	2024-03-01	2024-05-01

then I will have a date_table

Date

2024-01-01

2024-01-02

2024-01-03

and so on for all dates, to use this date_table as slicer

Is it possible using DAX to connect this date_table slicer to different column of date in fact_table?

I will have 2 charts based on category_a and category_b

if I select 2024-03-01

chart category_a will show very healthy

chart category_b will show non-healthy

[Royel · Accepted solution]

Hi @conniedevina  yes it is possible to connect different data columns for certain calculation using DAX USERELATIONSHIP functions 

Results: 

Model Relationships:

DateTable[Date] = CategoryA_Date (Active)

DateTable[Date] = CategoryB_Date (In-Active)

Dax: 

CategoryA = 
CALCULATE (
SELECTEDVALUE ( fact_table[category_a] )
)

CategoryB = 
CALCULATE (
SELECTEDVALUE ( fact_table[category_b] ),
USERELATIONSHIP ( fact_table[categoryB_date], DateTable[Date] )
)

Small Correction: Your Date table date format are not same as Fact table (CategoryA_Date, CategoryB_Date)

Here is corrected date for fact table 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[danextian · Accepted solution]

Hi @conniedevina 

You can create only one active relationship between two tables (if that doesn't result to an ambiguous relationship path with other tables) but you can have more inactive relationships. Inactive relationships can be invoked via measure using USERELATIONSHIP function

The image below shows a single date column but returns different values depending on which inactive relationship is invoked.

Please see the attached pbix.

[Royel · Accepted solution]

Hi @conniedevina  yes it is possible to connect different data columns for certain calculation using DAX USERELATIONSHIP functions 

Results: 

Model Relationships:

DateTable[Date] = CategoryA_Date (Active)

DateTable[Date] = CategoryB_Date (In-Active)

Dax: 

CategoryA = 
CALCULATE (
SELECTEDVALUE ( fact_table[category_a] )
)

CategoryB = 
CALCULATE (
SELECTEDVALUE ( fact_table[category_b] ),
USERELATIONSHIP ( fact_table[categoryB_date], DateTable[Date] )
)

Small Correction: Your Date table date format are not same as Fact table (CategoryA_Date, CategoryB_Date)

Here is corrected date for fact table 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[grazitti_sapna]

Hi @conniedevina,

Yes, it's possible, you can create two relationships with calendar table with different dates in your fact table.

One should be active and another should be a Inactive relationship.

Now for relationship with date which is active you can create a simple calculation and use it.

But for the relationship where the relationship is active you can use function USERELATIONSHIP in your measure, this makes the inactive relationship to act as a active relationship.

For e.g.

CategoryA_Selected =
CALCULATE (
SELECTEDVALUE ( fact_table[category_a] )
)

CategoryB_Selected =
CALCULATE (
SELECTEDVALUE ( fact_table[category_b] ),
USERELATIONSHIP ( fact_table[categoryB_date], DateTable[Date] )
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!