Displaying nothing in card values until a single slicer selection is made

Question

Current setup: I have a basic report with a number of charts on the page that are all controlled by once Slicer element at the top of the page.

As required, the default selection of the slicer is "All", which shows all data across the charts as is required. Once you filter on this value, the charts all filter as normal to the value of said slicer. All good so far, all working and happy with that.

Issue: At the top of the page, I have 4 cards, these are all counts and sums. These cards behave the same way. When no value is selected in the slicer, ALL of the data in these cards is summed and counted (as you would rightly expect) and when the slicer has a value, these cards display the correct values as per the slicer selection.

This default behaviour is not what I am looking for with respect to the cards. I only want the cards to display a value when the slicer has one single selection. Multiple selections or "All" should display blank, 0, n/a or something of the sort in the cards values.

As far as I understand, the default way of handling this is to create another measure which sets "Y" or "N" and then add this in as a visual level filter, only showing values when the measure is set to Y: EG

SlicerCheck = if(calculate(distinctcount([SlicerColumn]),allselected([SlicerColumn]))=1,"Y","N")

Other options suggest using functions that are not available in Direct Query mode.

This does not work for cards by default. Does anyone know a good way to achieve this solution?

Thanks

Damon

Solution

I would approach this by adding conditions to my count and sum measures. E.g.

SumForCard = IF(HASONEVALUE([SlicerColumn]), SUM(TableName[ColumnToSum]), BLANK())

Comments

[dmoney]

Thanks, does that work in Direct Query mode? I was sure it didn't. Appreciate the response.

[Alexis Olson]

I would think so, but I haven't tested it.

[dmoney]

Thanks @Alexis Olson - whether it works in direct query or not that is a good solution, marked accordingly.