Forum Discussion

Roym's avatar
Roym
Icon for Helper IV rankHelper IV
4 years ago
Solved

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 c...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Roym
Icon for Helper IV rankHelper IV
4 years ago

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's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    Roym
    Icon for Helper IV rankHelper IV
    4 years ago

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Roym
        Icon for Helper IV rankHelper IV
        4 years ago

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