Field needed for slicer is in other tables via multiple relationships

[gtmswadmin]

Hi, I have these 3 sharepoint list tables with many fields, but i will only mention the key fields

Customers table: [Id], [PersonInChargeId], [ManagerId]

Initials table: [Id], [User Id], [Initials]

Tracker table: [CustomerId], [PersonInChargeInitialsId], [ManagerInitialsId], [Datafield1], [Datafield2], etc.

The relationships are as below and I have already set them in the 'manage relationships' screen.

'Customers'[Id] = 'Tracker'[CustomerId]

'Initials'[UserId] = 'Customers'[PersonInChargeId]

'Initials'[UserId] = 'Customers'[ManagerId]

'Initials'[Id] = 'Tracker'[PersonInChargeInitialsId]

'Initials'[Id] = 'Tracker'[ManagerInitialsId]

My issue is when I am creating reports on the Tracker table, and I drag in a 'slicer', then I drag a 'Customer' or 'Initials' field into the slicer, using the slicer does not seem to affect the data, even though I have already created the relationships.

In this case, would the best way be to create 2 new calculated tables?

1 table for customer person-in-charge, joining the 3 tables

1 table for customer manager, joining the 3 tables

If yes, how do I do that? I have tried using GENERATE, SUMMARIZE and NATURALJOIN functions in DAX but no luck.

Thanks for your help.

[v-juanli-msft]

Hi @gtmswadmin 

What relationship do you create?

If you create relatipnships for all columns you list, it would not work as default.

You could create inactive relationship for some fields, then use "userrelationship" to make the inactive relationship active for specific measures.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@gtmswadmin , if you have joined two tables, dimension vs Fact with more then one table. We can choose a relation using userelation

There is an example here.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

But if this does not suit, then you might create multiple dimension or use few of them from Fact table