Display filters applied to data

3. Display filters applied to data

To understand properly our data, the very first thing we need to do is to perfectly know the filters applied to data. There are many ways to filter data (in the data source, in Power Query, in report/page/visual filters, slicers, …). So if you don’t understand your filters, you don’t understand your data. I will show you here how I display dynamically the filters applied to data.

How to display filters applied to data

  • Create a measure in the table which is meant to be filtered
Selected filters = 
VAR CR_LF = "
    "   // This is a simple carriage return
VAR Company = IF(ISFILTERED(Customers[CompanyName]) ; 
                "Company = " & CONCATENATEX(ALLSELECTED(Customers[CompanyName]) ; Customers[CompanyName] ; " , ") & CR_LF ; "")
VAR Category = IF(ISFILTERED(Categories[CategoryName]) ; 
                "Category = " & CONCATENATEX(ALLSELECTED(Categories[CategoryName]) ; Categories[CategoryName] ; " , ") & CR_LF ; "")
VAR Product = IF(ISFILTERED(Products[ProductName]) ; 
                "Product = " & CONCATENATEX(ALLSELECTED(Products[ProductName]) ; Products[ProductName] ; " , ") & CR_LF ; "")

RETURN  Company & Category & Product 
filters
  • Add a table visual to your ‘Filters’ page
  • Add the ‘Selected filters’ measure to the table

Don’t forget to update your ‘Selected filters’ measure and your ‘Clear filters’ bookmark when you add more slicers to the ‘Filters’ page.

Select some filters and see how the ‘Selected filters’ responds.

Here is the pbix file of the demo : download demo ‘Report filters.pbix’

This post is a part of a series : see the seriessee next post

Leave a Reply

Your email address will not be published. Required fields are marked *