Dynamic Labels for Reports

[Yted]

I would like to show my users what filters / slicers they selected when on a report. I have used the DAX of

Selected Colour = var selectedcolour = SELECTEDVALUE('Product'[Colour]) return "Colour: " & IF(ISBLANK(selectedcolour), "All Colours", selectedcolour).

However, if more than one item is selected then it just defaults to all. Is there a way to show say up to 5 items they have selected before showing them All or more selected?

[sam.mckay]

Easy

Use CONCATENATEX

See below for technique

Lists = CONCATENATEX( VALUES( Customers[Customer Name] ), Customers[Customer Name], ", " )

[Yted]

Thanks @sam.mckay

Is there a way to check that if nothing is selected it puts All Customers?

[BM_Girlracer]

@sam.mckay

Lists = CONCATENATEX( VALUES( Customers[Customer Name] ), Customers[Customer Name], ", " )

Hi

Can anyone someone let me know the answer to this question please?

[BrianJ]

@BM_Girlracer ,

Lots of potential different approaches to handle this, but here’s the approach I took:

One measure to count the number of values selected in the slicer:

Count Harvested Customers = 
COUNTROWS( 
    VALUES( Customers[Customer Names] )
)

One measure to count the total distinct customers:

All Distinct Customers = 
CALCULATE(
    DISTINCTCOUNT( Customers[Customer Names] ),
    REMOVEFILTERS( Customers )
)

And one measure to return the concatenated list if those two measures are not equal (will be equal when either all or none of the customers are selected in the slicer):

Result = 
IF(
    [Count Harvested Customers] = [All Distinct Customers],
    "All Customers",
    [Lists]
)

You could do this as all one measure with variables, but I thought the first two calculations were potentially useful enough outside the context of this measure to warrant their own separate measures, but either approach is fine.

Here’s what it looks like all put together in all three potential cases:

I hope this is helpful. Full solution file attached.

– Brian
e DNA Forum – Display Slicer Values Solution.pbix (364.9 KB)

[BM_Girlracer]

Thanks Brian!

If I have a date slicer like in the image, what would be the formula to make dynamic labels/title?

[BrianJ]

@BM_Girlracer ,

Sorry - not enough info here to provide an answer. What comparison are you making relative to the date slicer above? (i.e., you’ve only provided half of the conditional clause in the IF statement necessary to make the labels/titles dynamic).

• Brian