Tutorial: How to Display All Selected Filters at Once

Have you ever tried to understand a complex report, with dozens of page-level filters, slicers or cross-filtered data?

That was me, struggling until today.

I'm happy to share this quick guide, which allows you to display dynamically, in the report pages, the list of all applied filters:

1. Create a new DAX query in your PowerBI Desktop file,
2. Copy/paste the DAX code at the end of this message,
3. Execute it and copy the result,
4. Create a new measure and paste the copied result,
5. Display the newly created measure (called AppliedFiltersHTML) in a HTML-rendering visual, such as the excellent HTML Content Lite.

And that's all!

If you make changes to your data model, just re-run the steps 3 & 4 to refresh the list of fields monitored by the measure.

Hope this helps!

DEFINE
	VAR excludedTables = {"Table you want to exclude here", "Some other table"}
	
    VAR tabCols = INFO.VIEW.COLUMNS()
    VAR tabTabs = INFO.VIEW.TABLES()
	VAR tabHierarchies = INFO.LEVELS()

    VAR filteredTabTabs = FILTER(tabTabs, [IsHidden] = FALSE())

    VAR filteredTabCols =
        FILTER(
            tabCols,
            [Table] IN SUMMARIZE(filteredTabTabs, [Name]) &&
			NOT [Table] IN excludedTables &&
            [DataCategory] = "Regular"
        )

    VAR addCols =
    GROUPBY(
        filteredTabCols,
        [ID],
        [Table],
		[DataType],
        "Col",
        MINX(CURRENTGROUP(), "'" & [Table] & "'[" & [Name] & "]"),
        "ColDesc",
        MINX(CURRENTGROUP(), [Name])
    )

    VAR addExpressions = 
        ADDCOLUMNS(
            addCols,
			"EXP_ColHierarchy",
			VAR colId = [ID]
			VAR tabHierarchiesFiltered = FILTER(tabHierarchies, [ColumnID] = colId)
			VAR hierarchyId = MINX(tabHierarchiesFiltered, [HierarchyID])
			
			VAR tabHierarchiesFiltered2 = FILTER(tabHierarchiesFiltered, [HierarchyID] = hierarchyId)
			VAR hierarchyOrdinal = MINX(tabHierarchiesFiltered2, [Ordinal])
			
			VAR r = hierarchyId & "_" & FORMAT(hierarchyOrdinal, "00")
			
			RETURN r,
            "EXP_ColValues",
            "
            VAR isColFiltered = ISFILTERED(" & [Col] & ") 
            VAR selectedValues = VALUES(" & [Col] & ") 
            
			VAR filteredLabel = ""<u>" & [ColDesc] & "</u> → ""
            VAR filteredValues = 
				" & 
					SWITCH(
						[DataType],
						"Date",
						"
						VAR minDate = MIN(" & [Col] & ")
						VAR maxDate = MAX(" & [Col] & ")
						VAR r = IF(minDate = maxDate, minDate, ""from "" & minDate & "" to "" & maxDate)
						RETURN r
						",
						"CONCATENATEX(selectedValues, " & [Col] & ", "", "") "
					)
				& "
			
			VAR r = IF(isColFiltered, filteredLabel & filteredValues, BLANK())
            RETURN r
            "
        )
    
    VAR r = 
        "AppliedFiltersHTML = 
        VAR t = {" & CONCATENATEX(addExpressions, [EXP_ColValues], ", ", [Table], ASC, [EXP_ColHierarchy], ASC) & "}
        VAR f = FILTER(t, NOT ISBLANK([Value]))
        VAR r = ""<p style=""""line-height: 1.6;"""">"" & CONCATENATEX(f, [Value], ""<br>"") & """"
        RETURN r"

    VAR rt = {r}

EVALUATE rt