topic Re: Count rows for top n categories in DAX Commands and Tips

Count rows for top n categories

soldous — Wed, 07 Oct 2020 13:17:51 GMT

Hi all,

I need to count rows in the table which is filtered only for top n most frequent categories.

I know how to get these categories:

TOPN ( 5, VALUES ( Table[Category Level 1] ), RANKX( ALL( Table[Category Level 1] ),COUNTROWS(Table),,ASC) )

But I don't know how to use this as i filter for CALCULATE(COUNTROWS(Table),..)

I was thinking about using IN operator and something like this:

CALCULATE(COUNTROWS(Table),FILTER(Table,Table[Category Level 1] IN CALCULATETABLE(TOPN ( 5, VALUES ( Table[Category Level 1] ), RANKX( ALL( Table[Category Level 1] ),COUNTROWS(Table),,ASC) ))))

but it doesn't work.

Could someone help?

The solution without FILTER would be much appreciated because the table is quite huge and the FILTER function is incredibly slow.

Thanks a lot.

Re: Count rows for top n categories

CNENFRNL — Wed, 07 Oct 2020 13:36:59 GMT

Hi, soldous , a table itself can be used as filter in CALCULATE. You may want to try a measure in this pattern,

Measure = VAR __topn = TOPN ( 5, VALUES ( Table[Category Level 1] ), RANKX ( ALL ( Table[Category Level 1] ), COUNTROWS ( Table ),, ASC ) ) RETURN CALCULATE ( COUNTROWS ( Table ), __topn )

It's a general idea; you're supposed to base the measure on specific context of your data model.

Re: Count rows for top n categories

soldous — Wed, 07 Oct 2020 13:43:40 GMT

CNENFRNL Thank you very much!

There is always something new to learn 🙂

Re: Count rows for top n categories

TSki — Mon, 17 Oct 2022 22:46:23 GMT

Is there a way to connect this to a slicer?