Implementing Dynamic Titles in Power BI based on values selected in slicer
In power BI desktop, you can create a dynamic title based on values selected in slicer, it could be a single values or multiple values.
Suppose you have a report that have matrix visual which displays sales data.
Report user can see the sales data either in Quantity or Percentage, but one unit data at a time.
So, if value Sales% is selected in slicer – View By, then data values in matrix are displayed in %, in same way when Sales Qty is selected in slicer then data values in matrix are displayed in Quantity.
It simply means that there are two separate units for data values which are being displayed in report.
Now you want to give a title for this visual in such a way if user has selected Sales% then title name will be changed to %, and if user has selected SalesQty then title name will be changed to SalesQty.
Lets understand it with the help of an example. Here we have a Sales Report, user can see sales data either in % or quantity.
You can see, when user selects sales% from slicers then data in martix are displayed in % as shown in screenshot.
Now, when slicer value is selected as SalesQty then data values in matrix are displayed in Qunatity as shown in below screenshot.
As you have seen above screenshots, there are two separate data units that changes the data in matrix upon slicer selection, when Sales% is selected from slicer – ViewBy then sales data is displayed in % while total sales in quantity is displayed for SalesQty.
Lets implement the dynamic title based on slicer selected value.
Creating dynamic TITLE based on values selected in slicer
Lets create a measure named as Title that will return a dynamic title based on value selected in slicer.
First, we will get a selected value from slicer, and concatenate it in the end of text “SALES BY CATEGORY AND SUBCATEGORY”.
As we know, only the sales unit is being changed upon slicer selection that could be either in quantity or %, so we will change sales unit dynamically upon slicer selection while the remaining text for the Title could be any hard code value.
Title = VAR SelectedVal = SELECTEDVALUE(ViewOption[Option]) RETURN "SALES BY CATEGORY AND SUBCATEGORY [" & SelectedVal & "]"
Once you commit the Dax, then Go to Matrix visual Filter property.
Next, Turned on the Title, then click on expression (fx) icon.
Once you click on expression(fx) icon, a Text Title window opens.
Now, Go to Based on Field Dropdown, and select the measure Title that you had created, and click on OK button.
Once you click on OK Button, you can see there is an expression in Title box that means now title name will be given dynamically through measure, also you can see now report title is showing sales unit as [Sales%] as Sales% is selected in slicer.
Lets select the SalesQty from slicer and you can see, now sales unit in title is changed to [SalesQty] upon selection of SalesQty in slicer.
Creating dynamic Title for Multiselect values in Slicer
Here we have another report page, that have a card visual displaying a total sales by segment.
Now we have create a multiselected value title for this visual, and here user can select multiple values on slicer – segment, means title should display all selected values on slicer.
Lets create a measure – TitleTxt that uses VALUES and CONCATENATEX function, and returns all selected values in slicer – Segment by separating them by comma, means it concatenates all the selected values and separate values with comma.
VAR GetValues= CONCATENATEX( VALUES('Global-Superstore'[Segment]), 'Global-Superstore'[Segment], " & ") RETURN "SALES BY SEGMENTS [ " & GetValues & " ]"
Lets commit the DAX, and do the same steps as just did above, Go to Visual Filter property and turned on the Title.
After that select expression(fx) for Title that would be a Measure –TitleTxt, you just created.
Next, click on OK button.
Once you click on OK button, You see the dynamic title is displayed for visual and it shows selected segment value [coporate] in title.
Lets select multiple segments values in slicers.
You can see when two values are selected in slicers, it displays selected values in title as [Home Office & Coporate].
When all values are selected in Slicers then it displays all the selected values in title as [Consumer & Home Office & Coporate].
1,758 total views, 9 views today