Forum Discussion

kapildua16's avatar
kapildua16
Helper I
1 year ago
Solved

Disconnected Table DAX

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.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • pbiuseruk's avatar
    pbiuseruk
    Resolver IV
    1 year ago

    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.