Power BI (DAX) – Quick Tip – How to count how many items are selected on a Slicer

Update: 16 December 2016 (I got a comment from Marco Russo, who suggested that can use the FILTERS DAX Syntax which will “Return a table of the filter values applied directly to the specified column.” As well as this should also be faster. I am all about the speed!

I was working on a Power BI Project and one of the requirements for a rather complex DAX calculation was to know how many items the user had selected for the Fiscal Year in the Fiscal Year Slicer, which would then determine which Calculated Measure to use.

The trick here is that I wanted the count to be shown correctly in the Row Context. So that the count of items selected in the Slicer is on EVERY row.

What I mean by this is you can very easily get a count by using a Distinct Count which I can show below.

As you can see above it is counting it once for each row, which is correct when using the Distinct Count.

But what I was required to do, was to have the 8 being shown on every row. But if I deselected Fiscal Year 2011, I wanted it to change to 7 across the rows.

I think that this is something that will be really useful as well as allow me to use the count to dynamically use Calculated Measures.

Example:

• I am going to be using my Fiscal Date from my Date Table as a Slicer.
• I am then going to get a count of the rows, and show the total rows on each row.

Solution

Below is the DAX Syntax as well as an explanation of what it is doing.

• Original DAX Syntax
  

  ZCALC – Fiscal Year V1 =

  CALCULATE (

  CALCULATE (

  DISTINCTCOUNT ( ‘Date'[Fiscal Year] ),

  ALLSELECTED ( ‘Date'[Fiscal Year] )

  ),

  ALLSELECTED ( ‘Date’ )

  )

  

• Updated and Improved DAX Syntax

  ZCALC – Fiscal Year V2 =

  CALCULATE (

  COUNTROWS ( FILTERS ( ‘Date'[Fiscal Year] ) ),
  

  ALLSELECTED ( ‘Date’ )

  )

  

• The trick here is that I first get a count of the rows based on what has been filtered in my ‘Date'[Fiscal Year] column highlighted in LIGHT BLUE
  

  COUNTROWS ( FILTERS ( ‘Date'[Fiscal Year] ) ),
  

  • So what I am doing here is to get the count of rows for what has been Filtered.
    
• Then what happens with the outer CALCULATE is that it now takes the totals and breaks the Date Filter Context on the rows and using the ALLSELECTED applies it to each row.
• So what you now get is the following when all the dates are selected for 8 years
  • 
• And then if I have to deselect Fiscal Year 2011, which now makes it 7 Years selected I now get 7 in each row
  • 

As you can see this is very handy and can be used a variety of ways.

DAX for your data

If you want to apply this DAX pattern to your data this is how you can achieve it.

If you have all your data in one table called “My Data” and you have a slicer called “Type” you can change the DAX syntax to the following.

Type Slicer Count =

CALCULATE (

COUNTROWS ( FILTERS ( ‘My Data‘[Type] ) ),

ALLSELECTED ( ‘My Data‘ )

)

        

• What you need is to change the COUNTROWS to be your Slicer Column
• And your Outer CALCULATE to be your Table Name.

Conclusion

You can see that by counting your Slicer how dynamic you can make your data.

You can download the PBIX file with the example data here (Item count on Slicer sheet): UnPivot Other Columns.pbix