DAX – Dynamically Showing Title by Slicer in Power BI

When building a Power BI report page for analysing data by a specific dimension (e.g., customer, product and category), we often need to dynamically show the title of currently selected item(s).

1t1

One trick to show the title based on the current filter context is to use Multi-Row Card visual (we can also use the single row Card visual. However, the single row Card visual doesn’t support custom text alignment yet). We can disable the left-side bar and change the style of the text to make it look more like a title.

3

Now we have the dynamical title based on the selected single item.

2

However, this approach doesn’t work when there is no item selected or when there are multiple items selected. When there is no item selected, the Multi-Row Card visual will display all the items. As the size of the Multi-Row Card visual is set to be large enough for only a single item, only the first item is visible. The rest of items are still showing but have been cut out.

5

The title will be misleading when there is no item selected. We expect to see something like “All Categories Selected” or “No filter applied” as title.

6

We meet similar problem when there are multiple items selected where only the first selected item will be visible in the title.

7

Instead, the title needs to be able to let viewers aware that multiple items have been selected and how many of them.

ml1.PNG

Now we have gone through the requirements and we need an solution to be able to handle all the three scenarios: single item selection, no selection and multiple item selection. We can create a single measure to cover all the three scenarios:

Product Category Title = 

  IF (COUNT('SalesLT ProductCategory'[Product Category])=1, 
    VALUES('SalesLT ProductCategory'[Product Category]),
    
    IF (COUNT('SalesLT ProductCategory'[Product Category])=
              CALCULATE(COUNT('SalesLT ProductCategory'[Product Category]), 
                           All('SalesLT ProductCategory'[Product Category])),
        "All Categories Selected",
        
        MIN('SalesLT ProductCategory'[Product Category]) & " and the other "
                & COUNT('SalesLT ProductCategory'[Product Category])-1 & " categories"
    )
   )

Firstly, we check whether only a single item is selected, if so, we return the single selected value, if not, we check whether all items are selected, if so, we return the text showing all items are selected in the current filter context, if not, that means multiple items have been selected, we build a text string to show the name of the first item and the count of the rest of items.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s