Forum Discussion
Helper IVCreate country filter for two tables
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 |
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
6 Replies
- amitchandak
Super User
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
Roymamitchandak 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?
PaulDBrownCommunity Champion
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
