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

     

     

     

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