Disconnected Table DAX

[kapildua16]

Hi All,

I have a dataset with three columns: Region Name, Country Name, and Premium Amount. I need to create a single filter/slicer in Power BI that includes:

• All region names
• All country names
• A "Global" option

Requirements:

1. When a country is selected, show data for only that country
2. When a region is selected, show data for all countries in that region
3. When "Global" is selected, show data for all countries
   What's the best way to implement this without modifying existing measures? Preference is to use page-level filters.
   
   

[Anonymous · Accepted solution]

Hi All,
Firstly  pbiuseruk  thank you for your solution!
And @kapildua16 ,According to you, you want to create a single filter and then incorporate all the filters into this one filter, right?
Then we can create a new Table and put all the values we need to filter into this table, and then add it to the page filter.

FilterTable = 
UNION(
    SELECTCOLUMNS(DISTINCT('Table'[Region Name]), "Filter", 'Table'[Region Name]),
    SELECTCOLUMNS(DISTINCT('Table'[Country Name]), "Filter", 'Table'[Country Name]),
    ROW("Filter", "Global")
)

After that, we write a measure to determine if the choice is correct:

IsVisibleMeasure = 
VAR SelectedFilter = SELECTEDVALUE(FilterTable[Filter])
RETURN
    IF(
        SelectedFilter = "Global",
        1,
        IF(
            SELECTEDVALUE('Table'[Region Name]) = SelectedFilter ||
            SELECTEDVALUE('Table'[Country Name]) = SelectedFilter,
            1,
            0
        )
    )

If you have further questions you can check out my pbix file, I hope it helps and I would be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[kapildua16]

Thanks @Anonymous for the solution

[pbiuseruk]

Hi,

You have 2 options for this:
You can make a heirarchy for the relationship you described. It would go Global -> Region -> Country. Then you can simply make this a slicer and it will do what you require.

I think it would be better if you had 2 slicers though - One for Region and one for Country. The default of nothing selected will automatically be Global.

Please let me know if that helps.

[kapildua16]

Thanks @pbiuseruk  for the solution