Forum Discussion
Advocate IICount rows for top n categories
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.
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.
3 Replies
CNENFRNLCommunity Champion
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.
