2018-12-28

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.

Card values

Step 5: The card should now show which year is selected. 
Card

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.

Overall Results

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.

Multiple years title

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.

(Visited 795 times, 1 visits today)

About the author 

Laura Graham-Brown

SharePoint Trainer, Consultant and Agony Aunt

Leave a Reply

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

2 Free Ebooks: How to govern Microsoft teams 

Download your 2 free Microsoft Teams governance Ebooks and learn the steps necessary to create a bullet-proof governance strategy.