I am attempting to create a dynamic title that will return the category when 1 item is selected, return multiple options separated by a comma when they're selected, and return "ALL" when none or SELECT ALL is chosen on the slicer.
I have the following formula that works great with SELECT ALL or one item selected.
Dept Balance Chart Title 3=
"Total Remaining Balance for " & SELECTEDVALUE('List'[Dept Abbreviation], "Full Division ") & "is " & FORMAT(Encumbrances[Amount Remaining by PO Only], "$#,0.")
I also have the following formula that works great for multi-select and single select but does return the correct text when SELECT ALL is chosen in the slicer.
Department Balance Chart Title =
VAR Max_Category_Cnt =
CALCULATE( COUNT( 'List '[Abbreviation] ), ALL ('List '[Abbreviation] ))
VAR Distinct_Category_Cnt =
COUNT ( 'List '[Abbreviation] )
VAR Multi_Selected_Category =
CONCATENATEX (
TOPN ( 3, VALUES ( 'List '[Abbreviation] ), 'List '[Abbreviation] ),
'List '[Abbreviation] ,
", ",
'List '[Abbreviation]
)
VAR Selected_Category_List =
IF (
Distinct_Category_Cnt > 3,
CONCATENATE (
Multi_Selected_Category,
", and other selected OUCs"
),
CONCATENATEX (
VALUES ( 'List '[Abbreviation] ),
'List '[ Abbreviation] ,
", ",
'List '[Abbreviation]
)
)
RETURN
IF (
Distinct_Category_Cnt = Max_Category_Cnt,
"Division Total Remaining Balance is ",
"The total remaining balance for " & Selected_Category_List & " is " & FORMAT(Encumbrances[Amount Remaining by PO Only], "$#,0.")
)
Normally, instead of returning "Division Total Remaining Balance is" when all are selected like intended, it concatenates and lists all of the options available in the slicer.