Creating Dynamic Title based on Slicer Selection

You can create a dynamic title based on values selected in slicers or can show slicer selected values in text box.

Lets create a dynamic title which display the title as:

If more than two values are selected in the slicer then instead of showing all those values it should show few values and then add More.. in the last such as Listed Item by Category1, Category2 and More… .

If all the values are selected then it should show nothing just Listed Item by Category.

If less than or equal to 2 values are selected in slicer than it should show Listed Item by Category1, Category2.

As you can see here we have a table as shown below.

Now, to create a dynamic title as per above criteria, Lets create a measure.

Title =
VAR Max_Category_Cnt =
CALCULATE ( DISTINCTCOUNT ( CategoryList[Category] ), ALL ( CategoryList[Category] ) )

VAR Distinct_Category_Cnt =
DISTINCTCOUNT ( CategoryList[Category] )

VAR Multi_Selected_Category =
CONCATENATEX (
TOPN ( 2, VALUES ( CategoryList[Category] ), CategoryList[Category] , ASC ),
CategoryList[Category] ,
",",
CategoryList[Category], ASC
)

VAR Selected_Category_List =
IF (
Distinct_Category_Cnt > 2,
CONCATENATE (
Multi_Selected_Category,
", and More…"
),
CONCATENATEX (
VALUES ( CategoryList[Category] ),
CategoryList[Category] ,
",",
CategoryList[Category] , ASC
)
)

RETURN

IF (
Distinct_Category_Cnt = Max_Category_Cnt,
"Listed Items by Category",
"Listed Items by " & Selected_Category_List
)

Once you select the field click on OK button, and you will see the Title on visual.

It displays title as Listed Item by Category which is default as nothing has selected in slicer.

Lets select more than 2 values in slicers and you can see the Title, it displays two values and then add More.. just right after that.

You can see for two or single values, Title displays selected values as shown below.

Lets see, if all values are selected in slicers and it displays values accordingly.

Also Read..

Dynamic Title based on selected values in slicers

Power BI Tutorial

 476 total views,  1 views today