Forum Discussion
To connect date with different datekey from same table
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
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!
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.
3 Replies
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!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!
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.
