Slicer for multiple tables

[Roym]

I have report with a few different tables as shown in the below example. This works perfectly in my report but now I want to add slicer were you can choose a country and based on that selection the data for table 2/3/4 is shown for only that selected country.

What I first did is made the relationship between table1 and table 2 and 3. But when I want to make another relationship between table1 and table4 I get the error that this is not possible because this will create an ambiguity between two tables (I also have other relationships running between these tables.

Does anyone maybe have an idea how I can do this slicer without creating another relationship?

Table1

Countries

Netherlands

Greece

Table2

Issue name	Country
Issue1	Netherlands
Issue2	Greece
Issue3	Greece

Table3

Control name	Country
Control1	Netherlands
Control2	Netherlands
Control3	Greece

Table4

Process name	Country
Processa	Netherlands
processb	Greece
processc	Netherlands

Relationship made between Table1 country and table2 and table3 country.

[Greg_Deckler]

@Roym So normally you would create a relationship to Table4 from Table1 and would be good to go. If the relationship ends up being inactive, you would use USERELATIONSHIP in a measure to get around that. You can have multiple paths but one will need to be inactive. You can also use TREATAS or DAX Table Hopping in a pinch. Force indirect relationship in dax? - Microsoft Power BI Community

[Roym]

Thanks. What I did now is create the relationship between table1 and table4, this one is inactive because of the ambiguity error. The only thing I'm not fully understand yet (based on the article) is the USERELATIONSHIP.  It is in the example used as a calculation. But my tables don't use a date/number. How do I need to build the USERELATIONSHIP in my case (so for the country)?

[Greg_Deckler]

@Roym The exact answer is going to entirely depend on your desired outcome. What is the result that you are trying to achieve?

[Roym]

@Greg_Deckler I want to create a slicer with the countries in table1, and then all the other 3 tables should be filtered based on the selected country in the slicer

[Greg_Deckler]

@Roym OK, could go the "Complex Selector" route. The Complex Selector - Microsoft Power BI Community

In your case for Table 4 you could do this:

Selector Measure = 
  VAR __Countries = SELECTCOLUMNS('Table1',"__Countries",[Countries])
  VAR __CurrentCountry = MAX('Table4'[Country])
RETURN
  IF(__CurrentCountry IN __Countries,1,0)

You use this measure to filter your Table4 visual.

[Roym]

@Greg_Deckler When trying this I noticed my original explanation was missing one part. In my original file there is also a relationship between table3 and table4 for the control name. Once I then try to create the next relationship this doesn't work. 

I created the measure you described but was unable to get this working (yet). I have attached the Pbix file with my current setup. Hopefully you are able to help. Thanks!!

Test.pbix 

[Greg_Deckler]

@Roym That's a rather important detail...

Selector Measure = 
  VAR __Countries = SELECTCOLUMNS('Table1',"__Countries",[Countries])
  VAR __CurrentCountry = CALCULATE(MAX('Table4'[Country]),USERELATIONSHIP('Table1'[Countries],'Table4'[Country]))
RETURN
  IF(__CurrentCountry IN __Countries,1,0)

[Roym]

@Greg_Deckler  yeah I indeed missed that important part 😞 sorry for that! I tried the code but didn't get it working (yet). 

Not sure if I put the measure in the right spot?

[Greg_Deckler · Accepted solution]

@Roym See attached PBIX below signature.

[Roym]

Great, thanks!!!!