Dax Measure to Count Number of Selected Values in Hierarchical Slicer

[tonysantangelo]

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.

[ppm1 · Accepted solution]

Please try this expression instead

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

Pat