Forum Discussion

fbittencourt's avatar
fbittencourt
Helper IV
10 months 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 countr...
Need Help
  • MasonMA's avatar
    MasonMA
    10 months 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

     

     

     

MasonMA's avatar
MasonMA
Super User
10 months 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

 

 

 

  • fbittencourt's avatar
    fbittencourt
    Helper IV
    10 months ago

    Thank you vey much for your brilliant solution!!