In this tutorial Power BI, I want to cover one of my favourite topics to teach dynamic titles. These titles are created using a measure that contains the values selected in a filter.
Simple One Value Title
This title will show which single value is selected in a filter. In this example, my report has a page filter of Year from the table Calendar and the title will be a card showing a measure.
Step 1: On the Modelling or Home ribbon, select New Measure.
Step 2: I enter the following formula to create the measure that uses SELECTEDVALUE to show the filter value selected.
Year Title = SELECTEDVALUE('Calendar'[Year]) & " Results"
Step 3: I add a card to the report and select the measure to show in the card.
Step 4: In the format section of the card, turn off the Category Label, so the card shows the value.
Step 5: The card should now show which year is selected.
No Filter Selected
If no filter is selected the title only shows “Results”. This is due to SELECTEDVALUE function returning Blank if no filter is selected. So I will save the value of SELECTEDVALUE into a variable and then test if it is blank and return a different title.
Modify the measure to the following formula
Year Title =
var selYear = SELECTEDVALUE('Calendar'[Year])
return
IF(ISBLANK(selYear),"Overall Results",selYear & " Results")
The card will now show Overall Results if no filter value is selected.
Multiple Values
Using the formula in the previous part, if you select mulitple filter values, it will show Overall Results which is not what is wanted. I want it to list the values selected. For this, I am going to use the CONCATERNATEX and VALUES function. VALUES function will return all the selected filter values, and CONCATERNATEX will combine them into a comma separated string.
Modify the measure to the following formula
Year Title =
var selYears = VALUES('Calendar'[Year])
var txtYears = CONCATENATEX(selYears,'Calendar'[Year],", ")
return
txtYears & " Results"
This will now show the years selected.
Handling No Filter… Again!
If no filter is selected all the years are listed, this makes the title long. So a final modification to the formula is to count the rows returned by values and compare this the number of all the years.
Year Title =
var selYears = VALUES('Calendar'[Year])
// Count the selected years
var numselYears = COUNTROWS(selYears)
// Count all the years
var numallYears = CALCULATE(DISTINCTCOUNT('Calendar'[Year]),ALL('Calendar'))
// Calculate the years string
var txtYears = IF(numselYears < numallYears,
CALCULATE(CONCATENATEX(selYears,'Calendar'[Year],", "),ALL('Calendar'[Year])),
"Overall"
)
return
txtYears & " Results"
Conclusion
Dynamic Titles make a report more professional. The titles can be expanded to show multiple filter values if that is required but beyond the scope of this post. Dynamic titles can make a single page reusable for different filters.