Table of Contents

• Overview
• Pattern Overview
• Examples with DAX.do
• Conclusion

Overview

Breadcrumbs are a powerful element adding context to your reports. Effectively, when a user applies filters, breadcrumbs establish context for quick reference. You can see an example below or by going to this article: Financial Modeling in Power BI (Filter Page and Breadcrumbs), Analytical Ants.

Pattern Overview

The output of this pattern looks something like below:

Category [ Attribute: list, … ] …

The intent is that a developer defines category sections which logically group filters. An example might be a ‘Calendar’ category with the following attributes: Year, Month, date, etc. The output is supposed to be dynamic in that unselected categories and attributes simply don’t show – only relevant information is presented.  

The general pattern is below. The key components are:

• Categories: These are the developer designed sections.
• Attributes: These are the attributes users select.
• Delimiter: This is the separator between attributes

Breadcrumbs = 
VAR __Delimiter = "; "
VAR __Category = 
    VAR __Attribute1 = IF ( ISFILTERED ( Table[Column] ) , "Year(s): " & CONCATENATEX ( VALUES ( Table[Column] ) , [Column] , ", " ) )
    VAR __Attribute2 = IF ( ISFILTERED ( Table[Column] ) , "Year(s): " & CONCATENATEX ( VALUES ( Table[Column] ) , [Column] , ", " ) )
    ...
    RETURN CONCATENATEX ( FILTER ( UNION ( {__Attribute1} , {__Attribute2} , ... ) , [Value] <> Blank() ) , [Value] , __Delimiter ) 
VAR __Category_2 = ...
VAR __BreadCrumbTable = 
	Generate ( /*Categories*/ {"Category1", "Category2" , "..." }
		, ROW ( 
			"Data" , 
				SWITCH(
					TRUE() , 
					[Value] = "Category1", __Category , 
					[Value] = "Category2" , __Category_2 , 
					...
				)
			)
		)
RETURN CONCATENATEX( FILTER ( __BreadCrumbTable , [Data] <> Blank() ) , [Value] & " [" & [Data] & "] " )

Examples with DAX.do

The following example is based on this senario:

• Categories: Calendar and Product
• Attributes: Calendar [Year, Month, Date] and Product[Category, Subcategory]

Here is the full Breadcrumb code:

Breadcrumbs = 
VAR __Delimiter = "; "
VAR __Calendar = 
    VAR __Year = IF ( ISFILTERED ( 'Date'[Fiscal Year] ) , "Year(s): " & CONCATENATEX ( VALUES ( 'Date'[Fiscal Year] ) , [Fiscal Year] , ", " ) )
    VAR __Month = IF ( ISFILTERED ( 'Date'[Fiscal Month] ) , "Month(s): " & CONCATENATEX ( VALUES ( 'Date'[Fiscal Month] ) , [Fiscal Month] , ", " ) )
    VAR __Date = IF ( ISFILTERED ( 'Date'[Date] ) , "Day(s): " & CONCATENATEX ( VALUES ( 'Date'[Date] ) , [Date] , ", " ) )
    RETURN CONCATENATEX ( FILTER ( UNION ( {__Year} , {__Month} , {__Date}) , [Value] <> Blank() ) , [Value] , __Delimiter ) 
VAR __Product =
    VAR __Category = IF ( ISFILTERED ( 'Product'[Category] ) , "Category(s): " & CONCATENATEX ( VALUES ( 'Product'[Category] ) , [Category] , ", " ) )
    VAR __SubCategory = IF ( ISFILTERED ( 'Product'[Subcategory] ) , "Subcategory(s): " & CONCATENATEX ( VALUES ( 'Product'[Subcategory] ) , [Subcategory] , ", " ) )
    RETURN CONCATENATEX ( FILTER ( UNION ( {__Category} , {__SubCategory} ) , [Value] <> Blank() ) , [Value] , __Delimiter ) 
VAR __BreadCrumbTable = 
	Generate ( /*Categories*/ {"Calendar", "Product" }
		, ROW ( 
			"Data" , 
				SWITCH(
					TRUE() , 
					[Value] = "Calendar", __Calendar , 
					[Value] = "Product" , __Product 
				)
			)
		)
RETURN CONCATENATEX( FILTER ( __BreadCrumbTable , [Data] <> Blank() ) , [Value] & " [" & [Data] & "] " )

Example 1: Users selects May and June for 2009

Example 2: User adds the following filters: Category = Computers and Subcategory = Laptops

Conclusion

This breadcrumb pattern provides a robust and adjustable code to dynamically show relevant filters for users exploring reports. This code can easily be adjusted for formatting purposes. Stay connected for any additional updates!

Note: for additional information on robust DAX filter context displays, see the following articles: Displaying filter context in Power BI Tooltips, SQLBI and Displaying a list of selected months, SQLBI.

About the Author:
William Rodriguez is the founder and principal consultant of Analytical Ants, an analytics company focused on procuring insights via data engineering, data science, and data analysis. William has led multi-million-dollar construction projects, acted as financial controller managing million-dollar monthly check productions, and built and deployed BI infrastructures at companies large ($30B Revenue) and ‘small’ ($60M Revenue). Academically, he has his master’s in business, two undergraduate business degrees, four active Microsoft certifications, and over 70 accredited SQLBI training hours. William loves spending time with his amazing family and knows that he would be nowhere without Christ.

Reach out to Analytical Ants LLC for all your data/analytical needs. We specialize in #DataEngineering #DataScience, and #DataAnalysis.