cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonysantangelo
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:

tonysantangelo_0-1669768204561.png

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
ppm1
Solution Sage
Solution Sage

Please try this expression instead

 

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

 

Pat

Microsoft Employee

View solution in original post

1 REPLY 1
ppm1
Solution Sage
Solution Sage

Please try this expression instead

 

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

 

Pat

Microsoft Employee

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.