Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Roym
Helper III
Helper III

Create 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

ControlnameElementCountry
ControlAElementAUSA
ControlB Greece
ControlCElementFCanada

 

Issues

IssuenameElementCountry
IssueAElementFCanada
IssueBElementAUSA
IssueCElementABrazil
1 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

yingyinr_0-1633427483386.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
amitchandak
Super User
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

@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?

 

Capture.PNG

PaulDBrown
Community Champion
Community 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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@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?

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

yingyinr_0-1633427483386.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.