DAX HELP: TOPN VALUE IGNORING REPETITIONS

[laciodrom_80]

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!

[V-pazhen-msft]

@laciodrom_80 

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 . @V-pazhen-msft  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!

[V-pazhen-msft]

@laciodrom_80 

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.

[laciodrom_80]

@V-pazhen-msft

but in your example you are still using the RANKX function to find rank4value,it is not supported in DQ mode....

[V-pazhen-msft]

@laciodrom_80 

Rankx is not supported because it cannot return repetitive values, you can still create ranks. And thsi dax works with my sample DQ file, give it a try.

Paul Zheng

[laciodrom_80]

@V-pazhen-msft 

Ok, you're right, but creating a new table as I thought to do, my report switches to mixed mode, instead I'd like to mantain DQ. In practice I wish to display as columns in a matrix the four 4 greatest numbers (in reality the corresponding labels in another column). Yes if I created a table like you suggested and how I thought to do in a first moment, I could reach the goal but I should turn into mixed mode and I wouldn't. What would you suggest? Is it possible? 

Thanks a lot

[amitchandak]

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