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

Advertisements

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

  1. An easier way to get the selected values in the slicer is using the FILTERS function.
    You could write COUNTROWS ( FILTERS ( ‘Date'[Fiscal Year] ) ) obtaining the same result. It should be also faster.

    • Thanks for the suggestion for a faster and easier way to write the measure. I will give it a test tomorrow and make sure that it fits for my requirements. I will post back in the comments if it indeed does do so. And yes I agree your solution is not only easier to read, but also should be faster. I am always wanting to ensure that it is both easy to understand and fast.

  2. Ok, that makes sense, I did do a lot of Googling trying to find the solution and for some reason I did not come across the FILTERS. But if it does indeed work (and I have no doubt it should), I will update the post with the easier measure.

    And I appreciate the feedback, and glad that you enjoyed the post.

  3. Hi there Marco, just to give you an update with regards to my use case for the counting of the Slicer Values. Your calculated measure (ZCALC – Fiscal Year V2) does work when on a single row. But when I put in my Fiscal Date onto the Rows, your calculated measure (ZCALC – Fiscal Year V2) changes to zero. Whilst my measure above (ZCALC – Fiscal Year) above keeps the correct selected totals across all the rows.

    ZCALC – Fiscal Year ZCALC – Fiscal Year V2 Fiscal Year
    5 0 2015-16
    5 0 2016-17
    5 0 2017-18
    5 0 2018-19
    5 0 2019-20
    5 0 2020-21

    Thanks for the suggestion once again.

    • Yes, in that case the FILTERS returns what you have in the current filter context. You’re right, probably this is the reason why it’s not used so often, it’s an unreliable way to get the “external” slicer 🙂
      Thanks for documenting it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s