cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Dax Measure to Count Number of Selected Values in Hierarchical Slicer

Let's say I have a hierarchy with columns of TableName[Year] and TableName[Month]. It's sort of a date hierarchy in concept but technically it's not. The slicer looks like this:

How can I write a DAX measure to count the number of months selected in the slicer? Here's my attempt:

``Count Slicer Selections = COUNTROWS(ALLSELECTED(TableName[Month]))``

This works fine when particular months are selected. However, when I select Year 1 or Year 2, it returns a value of 14. My desired behavior is that the measure returns 12 when Year 1 is selected and 2 when Year 2 is selected. Even though the boxes in the slicer for each month are automatically darkened, I believe only TableName[Year] is considered to be filtered in this scenario and not TableName[Month]. Hence, the measure returns same value as if no filter/slicer selection is made.

I'm fine with the measure returning 14 when no slicer selection is made. There's no need to use ISFILTERED to make it return 0 in that case, although I have a feeling the solution might still involve ISFILTERED.

Lastly, note that the number of months in Year 2 will change over time. I can't hardcode something like "If Year 1 selected, then add 12. If Year 2 selected then add 2." If there were a more generic way of counting the number of months in each selected year and summing them, that would be great.

1 ACCEPTED SOLUTION
Solution Sage

``Count Slicer Selections = COUNTROWS(DISTINCT(TableName[Month]))``

Pat

Microsoft Employee
2 REPLIES 2

The suggested solution returns total number of values if all or none is selected. how can we return 0 if no selection is made?

Solution Sage

``Count Slicer Selections = COUNTROWS(DISTINCT(TableName[Month]))``

Pat

Microsoft Employee

Announcements