Forum Discussion
How to create a 2nd relationship between two tables
Hi All,
I have several measures built (below), which use data from both my HC and Leavers table. I am able to filter by a Company slicer as the relationship between the HC and Leavers table is company. However i also need to have a slicer by Pillar. At the moment the pillar silcer can only filter data in the HC table or the leavers table, depending where i take the pillar field from, i need it to do both.
Please see below the measures i have, the slicers which need to work and a snippet of my table relationships.
I am aware there can only be one active relationship, so do i need to do this another way via a measure or something else?
What way is best please? I am relatively new to power BI so as clear and concice as possible would be hugely appreciated.
Thanks in advance.
Hi Anonymous ,
Sample data
You can create a second relationship which is inactive.
If you want to use the relationship between date columns, you can create a measure like the following.
Measure = CALCULATE(SUM('Table (2)'[Value]),USERELATIONSHIP('Table (2)'[Date],'Table'[Date]))USERELATIONSHIP function (DAX) - DAX | Microsoft Docs
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , if you need a two-column join , you can create a concatenated column in both tables and join
If you need to change the relationship, You can create an inactive join and activate that in a measure using userelations
Hi Anonymous ,
Sample data
You can create a second relationship which is inactive.
If you want to use the relationship between date columns, you can create a measure like the following.
Measure = CALCULATE(SUM('Table (2)'[Value]),USERELATIONSHIP('Table (2)'[Date],'Table'[Date]))USERELATIONSHIP function (DAX) - DAX | Microsoft Docs
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
