How to create a 2nd relationship between two tables

[Anonymous]

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.

[Anonymous · Accepted solution]

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.

[Anonymous]

Sorry i am still unclear. 
I have pillar in both tables. I need to slice various visuals (which contain measures that calculate data between the 2 tables) by pillar. 

I tried making a userelation measure, but it only lets me calculate. I need to be able to set Pillar as a slicer?

thanks.

[amitchandak]

@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

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970