Dynamic Header with All data and by what is being filter

[Bmejia]

I am trying to figure out how to create a dynamic header that shows all the values in a particular column base on what is being filter at the page level and what the user is filtering on.

Example

My table has category column, with values A, B, C, D, E, F, G

My page filter is filter on only A, B, C and have a drop down on the visual that shows same filter A, B, C  (The Page Filter will be use from time to time to add more categories, but by default need to keep it at A, B, C)

I am trying to create a dynamic header base on everything that has being filter at the page level and by the user when they use the drop down.

Example,  “All Categories Being Filter at page level A, B, C, Data, Filter by C Only”

This measure  is only shows me what the user filter the data on, which is good for my part of my filter.
Category Header =  "Filter by User" & CONCATENATEX (VALUES ( Table[Category] ), Table[Category],", ")

But, this measure is showing everything on the table category column (A-G).  I have used all except, allSelected, keepfilters,  and other measures and can’t seem to keep it to just what is being filter at page level.
Category Header = "All Categories Being Filter at page level" & CALCULATE(CONCATENATEX(VALUES(Table[Category]), 'Table'[Category], ", ", Table[Category], ASC), All(Table[Category]))

Thank you in advance

[Bmejia]

Thank you for you help, This works perfectly.  😀

[v-rzhou-msft · Accepted solution]

Hi @Bmejia ,

Both Page level filter and drop down slicer are all filters, so if you use same Category column from table in them, it is hard to achieve your goal.

I suggest you to create an unrelated Category table for slicer.

Category for User = VALUES('Table'[Category])

Create a measure to filter this slicer.

Measure Filter = 
IF(MAX('Category for User'[Category]) in VALUES('Table'[Category]),1,0)

Add measure into visual level filter and set it to show items when value = 1.

New Measure:

Category Header = 
VAR _FilterbyPagelevel =
    CONCATENATEX ( VALUES ( 'Table'[Category] ), 'Table'[Category], ", " )
VAR _FilterbyUser =
    CONCATENATEX (
        VALUES ( 'Category for User'[Category] ),
        'Category for User'[Category],
        ", "
    )
RETURN
    "All Categories Being Filter at page level" & " " & _FilterbyPagelevel & " "
        & IF (
            ISFILTERED ( 'Category for User'[Category] ),
            "Data, Filter by" & " " & _FilterbyUser
        )

Result is as below.

Best Regards,
Rico Zhou

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