In PowerBI you can create Dynamic Titles in two ways.
For this blog the example I will be using is a PowerBI report on the sales and transaction data of three different hotels.
Since you can filter down on each hotel, I want to have the title to also change to the hotel that I have selected.
ㅤ
First you need to create a text box.
ㅤ
ㅤ
Then when typing in the text box, pay attention to the bottom left, where there should be a button called 'Value'.
When you click on it, a window should pop up like this:
ㅤ
ㅤ
In the box under 'How would you calculate this value', input the field that the dynamic title should change according to. This field should be linked to the data in your report, otherwise it will not change according to your selection.
When you put the field you want in, give PowerBI a second to find the field. It should be highlighted and have a line underneath when it has processed it.
ㅤ
ㅤ
Afterwards press save, and your field should change dynamically based on your selection. You will need to reformat this field, and there will always be a line underneath to indicate that it is a dynamic value.
ㅤ
ㅤ
However by using a defined field, you may realise quickly how this dynamic title will only work if you are always filtering by something.
ㅤ
When I have 'All Hotels' selected (which is generated by PowerBI in the slicer tool) it doesn't show up in the dynamic field as it doesn't exist in the data.
You can also see when I select multiple hotels it doesn't add the names together either.
ㅤ
So how do we work around this?
ㅤ
We have to create a new measure, more specifically an IF statement.
ㅤ
ㅤ
From lines 3 to 8 is defining how the measure will know if a category has been selected.
Technically I could hardcode the measure to trigger if the distinct count is less than 3. However if you don't know how many unique fields you may have or if the dataset may update and add more you can use the CALCULATE() function to do this automatically.
Within the CALCULATE() field, you must once again reference the DISTINCTCOUNT() of the column you want to make dynamic. Then you must use the REMOVEFILTERS() function to count everything regardless of what is selected (as this would filter everything out except what is selected).
ㅤ
So as I have only selected the 'Watergate Inn' the initial DISTINCTCOUNT() will now return 1, which is < than the total number of hotels, which is 3. Triggering the IF statement.
ㅤ
From lines 9 to 13 is telling PowerBI what values to return as a result of the IF statement triggering.
I am using the function CONCATENATEX() which looks at the expression at a row level before joining them together.
[Line 10] Using DISTINCT() removes any duplicates in calling back the hotel name (as it will return all instances of that hotel name in the dataset)
[Line 11] Concatenating it with the second hotel selection
[Line 12] Separating these hotel names with a comma and space
Then after line 12 we are giving the 'else' part of the IF statement:
[Line 13] If the number of hotels filtered is not less than the distinct count of hotels in the dataset, return 'All Hotels'
ㅤ
ㅤ
Now add that measure you have just created back into the title.
ㅤ
ㅤ
Now we have a dynamic title that shows concatenated hotel names and 'All Hotels' when nothing is selected or when everything is selected!