Forum Discussion

fbittencourt's avatar
fbittencourt
Icon for Helper IV rankHelper IV
1 year ago
Solved

Inverse Filter

Hi All !!

 

I need some help to apply an inverse filter:

 

EG.  I have a dimension table connected to the fact table, but my manager ask me  the following:

 

 

 

When we filter a country like Japan , show as well all countries that belongs from ASIA on a visual, without using the country filter, the same logic for europe with we filter a country, Italy, show all countries from europe.

 

 

Thanks for your help😉

 

 

Need Help
  • MasonMA's avatar
    MasonMA
    1 year ago

    Hi, you can give it a try like below

     

    1. Create another Dim Country table in Power Query by using nestedJoin

     Table.NestedJoin(Dim, {"Region"}, Dim, {"Region"}, "Dim", JoinKind.LeftOuter)

    2. Leave this Dim Country table disconnected from other tables.

    3. Create a Measure so that you can use in your table visual later, mine looks like 

    _Level = 
CALCULATE(
    SUM(Fact[Level]),
    Fact[Country] IN 
        VALUES('Disconnected Dim'[Dim.Country])
)
    4. Use Region, Country from this Disconnected table for Table visual, and Country also from this Disconnected table for slicer