Inverse Filter

[fbittencourt]

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😉

[MasonMA · Accepted solution]

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]

Thank you vey much for your brilliant solution!!

[grazitti_sapna]

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]

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