Text field to show the selected filter

[Anonymous]

How could I show what multiple filters are selected on a report page?

There are date  filters and categorical filters.

I want the label to show everything that currenlty selected at al times.. and "ALL" when everything is selected.

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may learn to use ISFILTERED Function inside the IF Function.

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may learn to use ISFILTERED Function inside the IF Function.

[roundwound]

Must have missed the actual solution somehow.  No doubt this is great advice, but something a bit more detailed and specific might actually be helpful.

[OwenAuger · Accepted solution]

@roundwound @Anonymous

I would use a measure structured something like this:

Filters Selected =
IF (
    ISFILTERED ( MyTable[MyColumn] ),
    CONCATENATEX ( FILTERS ( MyTable[MyColumn] ), MyTable[MyColumn], ", " ),
    "ALL"
)

The reason for using FILTERS is because it returns direct filters on the specified column, as opposed to VALUES which would return the values visible in a column incorporating cross-filtering from other columns.

[Agreenwood]

Thank you for sharing such an elegant solution.

Can you think of an equally elegant solution to incorporate an "All Except" rule? For example, I could have a filter with upto 30 different objects, and it could be I want to include all other than 2 of them. In this case it would be preferential to have the measure return "All Except AAA and BBB".

What do you think?

[OwenAuger]

@Agreenwood

Something along these lines

Filters Selected (All Except version) =
IF (
    ISFILTERED ( MyTable[MyColumn] ),
    VAR ExcludedValues =
        EXCEPT ( ALL ( MyTable[Column] ), FILTERS ( MyTable[MyColumn] ) )
    RETURN
        "All Except " & CONCATENATEX ( ExcludedValues, MyTable[MyColumn], ", " ),
    "ALL"
)

This would return "All Except AAA, BBB, CCC,..." without an "and" between the last two items.

To include an "and" between the last two items would require a bit more logic: concatenating all but the last excluded item, followed by "and <last item>". Post back if you need help on that aspect.

Regards,

Owen

[Agreenwood]

Works great, thank you so much.