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

     

     

     

4 Replies

  • 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

     

     

     

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    10 months ago

    Hi fbittencourt,

     

    Create a measure with below DAX

     

    ShowIfSameRegion :=
    VAR SelectedCountry = SELECTEDVALUE('Country'[Country])
    VAR CountryRegion =
    CALCULATE(
    MAX('Country'[Region]),
    'Country'[Country] = SelectedCountry
    )
    RETURN
    IF(
    MAX('Country'[Region]) = CountryRegion,
    1,
    0
    )

     

     

    • Add your visual (e.g., table or chart).

    • Put the measure ShowIfSameRegion in the Visual-level filters pane.

    • Set it to is 1

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

     

    • fbittencourt's avatar
      fbittencourt
      Helper IV
      10 months ago

      The mesure is based on the fact or the dimension table (Country[Country)?

       

      I have applied the mesures to test it on the columns but is not filtering.

       

      Tks again!!