Forum Discussion
Helper IVDAX HELP: TOPN VALUE IGNORING REPETITIONS
Hi all,
I' ve got a table A with a column Sales containing int numbers: I'd like to create a new table B from table A populated with all the rows containing the 4 greatest numbers (maintaining repetitions) which are in the column:
For example, having thiscolumn "Sales" in table A:
10
3
1
20
20
5
4
5
Rows in table B should contain these values in column Sales:
20
20
10
5
5
4
I am not able to use TOPN function for this issue, any clue?
Thanks in advance!
7 Replies
- amitchandak
Super User
Create a rank and filter based on that
rank = rankx(all(TableA),TableA[Sales],,desc,dense)
And filter on rank.
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale Create Table B with something like this:
Table B = CALCULATETABLE(VALUES('Table A'),FILTER('Table A',RANKX('Table A',[Column],,DESC,Dense)<=4))Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- laciodrom_80
amitchandak . Anonymous thanks for suggestions, RANKX came to mind to me too, but I noticed that in Direct Query mode it isn't supported 😢
Is there a workaround to achieve the goal in direct query? I've also tried to use COUNTROWS but with the same result of RANKX ğŸ˜
Thanks!
There is a workaround with DQ mode, you can find the value of the 4th rank, then return all values that are >= to the 4th ranked value.
Table B = var rank4value= CALCULATE(SUM('Table A'[Value]),FILTER('Table A',RANKX('Table A',[Value],,DESC,Dense)=4)) Return CALCULATETABLE(VALUES('Table A'),FILTER('Table A','Table A'[Value]>=rank4value))Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
