Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.