My dynamic visual won't work (relations)

[Jackisover]

Hello everybody, 
I had a quick formation in Power BI but not enough for me to understand everything.
I've already created a lot of visuals but I have a problem with this one (bottom) :

The idea is to know how many beds are available in the south of France, in terms of the type of accomodation (hotel, camping, etc.) and in terms of territories (cities, groups of cities, etc.)
I have 3 files connected : 
1)  One with the number of beds in termes of type of accomodation and city keys (2022), it contains more data like GPS coordonates to display a map

2) One with the name of cities, their beloging to a group, and city keys (which allows me to create my filter on the left)

3) One with the evolution of the number of beds for the past 10 years, in terms of type of accomodation and city keys.

File 1 is connected to file 2 with "city key"

File 3 is connected to file 2 with "city key"

At this point, if I click on my cities filter on the left, everything is OK, my graphics are dynamic.

But here is my problem : I would like also to click on one type of accomodation, on my top graphic, and see my bottom graphic change too, showing only the datas of this type.

I wanted to add another relation, between file 1 and file 3 "type of accomodation" but it won't work because there already is a relation between files 1 and 2.

How can I resolve this ? Sorry if it's not very clear, It is not easy to describe all of this in English !

Thank you very much !

[Jackisover · Accepted solution]

Hello,
I found my solution. I needed 2 things : 

- Create a bridging table to have unique type of accomodation keys to create a one to many relation.

- Make sure my city keys table was setted as well on one to many relation. I don't know why but it wasn't the case, so I recreated this file (file 2) and everything worked fine.

Thank you @vanessafvg for your help

[vanessafvg]

so sorry i didn't get back to you just been incredibly busy but very glad you figured it out, well done!

[vanessafvg]

the issue is because you have a bidirectional relationship and a many to many relationship - i dont suppose you are able to share the power bi file?  

ideally you need to do some remodelling

1. you can do is create a lookup table with all unique city keys and then link all the tables you have to that table.  then you filter on that lookup table.  make sense?

[Jackisover]

Thanks for your answer @vanessafvg , I'm not sure I get it.
I sent you a private message with my file.

Actually, this is what i wanted to do : file 2 contains only unique city keys, my other tables are related to it. It works perfectly fine if I want to filter on cities, but won't work if I want to filter on type of accomodation. Should I change the relation to file 1 (many) - file 2 (unique) ? 

[vanessafvg]

can you share a picture of your data model?

[Jackisover]

Absolutely @vanessafvg :

File 1 on top. File 2 on the left. File 3 on the right. As you can see, file 3 can't be connected to file 1 (dotted relation). "Code INSEE" means "city key".

Here are what my files look like : 

Thanks for your help

[vanessafvg]

i have added in a bridging table

not sure if that is giving the desired result it might need some more work.   

if it incorrect please provide an example of one test you can expect when correct

[Jackisover]

Thanks again for your time @vanessafvg 

It actually doesn't work : when I click a type on the top graphic, it doesn't change my bottom graphic. 

But I think the solution of creating a "bridging table" is the one. I tried it on another project, but I created a "type of accomodation bridging table" instead of a city key one, and it seems to work. 
The idea is : 
File 1 is connected to file 2
File 3 is connected to file 2
File 1 is connected to file 3 but not directly, towards a file 4 "type of accomodation"

Exemple : New project 

Do you confirm the method ? 

Thanks !

[vanessafvg]

what did you click on and what did you expect to change?  when i did it i thought it changed.  ideally the whole thing needs to be remodelled in my opinion

before clicking

after cllicking

[Jackisover]

When you click on one type of accomodation on the top graphic "camping" for instance, i would like the bottom graphic to be filtered, so i only see the yellow bar and not the other types