Create country filter for two tables

[Roym]

In my PowerBI report I have two tables: Controls and Issues (as shown below). There is an active relationship created between the 'elements' field for both tables (there is not always a link, there could be controls and/or issues that are not related). This works well. But now in my dashboard I have two tables, one for controls and one for issues. What I would like to do is create a slices with the countries and then when selecting USA for example it would show in the controls table the USA controls and in the issue table the USA issues.

What I though I could do is create a new relationship between the countries in both tables, but I can not make that relationship active as it said because an active set of indirect relationships already excists. I tried without the relationship to create a slices based on the control table country and use that as a filter in the whole dashboard but that didn't work. It only shows results for the controls and the issues remain empty. What would be a way to achieve this?

Controls

Controlname	Element	Country
ControlA	ElementA	USA
ControlB		Greece
ControlC	ElementF	Canada

Issues

Issuename	Element	Country
IssueA	ElementF	Canada
IssueB	ElementA	USA
IssueC	ElementA	Brazil

[amitchandak]

@Roym , You need to have common tables element and country and join them both tables with those and filter using common tables 

refer if needed

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

[Roym]

@amitchandak Thanks! Watched the video and this makes it a bit clearer. But still not 100% sure what to do.

So I created a new table (Bridge_Table = DISTINCT(UNION(ALL(Table_Controls[Country]),ALL(Table_Issues[Country])))

This created the list of all countries. But I still need to do something with the Element part I guess, as currently I can not make the relationship two ways, as the element relation between the controls and issue table is still active. How do I need to do this? Add another column to this Bridge_Table with the elements?

[PaulDBrown]

The table code should be:

DISTINCT(UNION(VALUES (Table_Controls[Country]),VALUES (Table_Issues[Country])))

Before you create the relationships bewteen the bridge table and each fact table, delete the relationship you have between the fact tables

[Roym]

@PaulDBrown When removing the relationship between the elements I was indeed able to create the relationship between the country bridge table and both table country fields. But I'm not sure what I need to do now to also make a link between the elements? Should I create a additonal bridge table? Or add the elements via a union to the bridge table as well?

[v-yiruan-msft · Accepted solution]

Hi @Roym ，

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create country dimension table just as suggested by @amitchandak  and @PaulDBrown and apply the country as slicer option

Note: Please DON'T create any relationship between country dimension table and your fact tables(Controls and Issues table)

Countries = DISTINCT(UNION(ALL(Controls[Country]),ALL(Issues[Country])))

2. Create a measure as below

Measure = 
VAR _selcountries =
    ALLSELECTED ( 'Countries'[Country] )
RETURN
    IF (
        SELECTEDVALUE ( 'Controls'[Country] )
            IN _selcountries
                || SELECTEDVALUE ( 'Issues'[Country] ) IN _selcountries,
        1,
        0
    )

3. Create table visuals and apply visual level filter with condition(Measure=1) just as shown in below screenshot

Best Regards

[Roym]

This is great! Works perfectly. Thanks for the help!!!