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

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!!!