How To: Display All Selected Filters at Once

Here is what we want to achieve: a dynamic list of all our filters, directly on our report pages.

dsfdsfdssdf

Example of filters list displayed on the report pages.

Here is the process:

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 any change to your data model, just re-run the steps 3 & 4 to refresh the list of fields monitored by the measure.

You can adapt the following code to your needs (to display datetime in your local format for example):

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