0

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.