Drill down filter between tables - cannot use many to many relationship

[Sara16]

Hi Power BI Community

I am hoping I can gain some help to drill down between two tables on one page within Power BI. Esssentially from the below, when I click on a name in Leave Balances, I would like the name to appear in the right hand side table in Leave Days Taken. I tried to build a many to many relationship between two tables but am unable to as all my tables are connected to one master mapping table. 

Any assistance would be greatly appreciated 

[Ritaf1983 · Accepted solution]

Hi @Sara16 
If you have already a table that both of those tables are connected to, then you just need to take the name from this mapping table and put it on the visuals.

if I missing something and this option is impossible, then you can create a "bridge table" with unique names 

and add it to the model with regular 1-to-many relationships:

and again use the name from this bridge in the visuals

You can use my file for the following.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Sara16]

Hi @Ritaf1983 

I managed to get one of the bridging tables to work however the other does not display anything? I have done the extac same method but it fails 😞  Is there a way to get around it

[Ritaf1983 · Accepted solution]

Hi @Sara16 
If you have already a table that both of those tables are connected to, then you just need to take the name from this mapping table and put it on the visuals.

if I missing something and this option is impossible, then you can create a "bridge table" with unique names 

and add it to the model with regular 1-to-many relationships:

and again use the name from this bridge in the visuals

You can use my file for the following.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Sara16]

Hi @Ritaf1983 

Thankyou for the below. It would be the second option I need to try as the mapping table is not possible. I tried with the bridging tables but still does not work. I tried with both single and both for the many to one relationship and get the below error

Any advice on where to go to next? 

[Ritaf1983 · Accepted solution]

Hi @Sara16 
Sorry, 

This is all I can offer without access to your files.

Take a look at the linked  guides and discussions about the same issue, maybe it would help:

https://www.youtube.com/watch?v=eT2NV2MNaLw

https://www.linkedin.com/pulse/power-bi-learn-reduce-relationship-cardinality-arno-wakfer-/

https://community.fabric.microsoft.com/t5/Desktop/The-cardinality-you-selected-isn-t-valid-for-this-...

[Sara16]

Thankyou for your help! Ill keep working on it 🙂