This blog follows on from my previous post on adding a drill through action to your Power BI Dashboard, connecting two sheets. It will show you how to add a dynamic title to clarify to the user which category has been selected in the drill-through action. This can also be useful if you have filters applied to the whole page and want to make that clear in the title. If you haven’t read the blog on drill through actions but are curious, it is called 'Power BI: How to Drill Through'.
We’re starting on a page that has been filtered to only the State of California, showing a product breakdown in terms of Sales, Profit, and Quantity Sold. However, as you can see in the image below, it is not clear in the view that the whole page is filtered to California. We can’t just add a text box saying ‘California’ as this would be misleading if the filter changed to a different state. Instead, we will need to write a short DAX calculation to add a dynamic title.
The first step in adding this title is creating a measure. To do this, go to the top ribbon, select home, and then choose ‘new measure’.
This will generate a calculation window drop-down below this ribbon. This is where we write our DAX.
The formula is as follows:
State Name = SELECTEDVALUE(Orders[State/Province], "All States").
- State Name' is the name I have given my measure. It can be anything you choose, but it is best practice to name it something relevant and descriptive.
- 'SELECTEDVALUE' is our function. This function accepts two arguments, the first is required while the second is optional. This is what the function looks like before we enter our arguments: SELECTEDVALUE(<columnName>,[<alternateResult>]).
- The column name is the required argument, and in our case, it is the field [state/province] from the Orders table. The second argument, alternate result, is the text that we can choose to have when there is no selection. In my case, I have chosen 'All States', but if left blank, there would simply be no text unless a state was selected.
The column name argument could be replaced with any other field we may be looking to filter by to create a new dynamic title, such as the year, for example.
Once the measure is created, make sure to save it by clicking enter. To add this measure into our view, I usually start by creating a new text box. Once the new text box is created, click on the text box and then click on value as shown below. Clicking value, a drop-down appears which starts with 'create a dynamic value...'. Add our measure in the box below the text which says 'how would you calculate this value'.
Here you will need to enter the exact name of the measure you created earlier. Once it is recognized, it tends to be underlined in blue.
Click save, and the text will appear in the textbox, although it is likely to be much smaller than all other text near your title. The last thing to do is some basic formatting to get it looking right. Formatting one of these titles can be a bit fiddly, but if you double-click on the text, it should allow you to adjust the size and font as required.
Here is my final, formatted version. I have drilled through from a new state to make sure it dynamically changes.
Make sure to also give this a go for the year; Create a title that dynamically updates with a slicer changing between different years and also has the option to show all years.