Forum Discussion

Helper IV

6 years ago

DAX 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 re...

Tutorial Requests

Anonymous

Not applicable

6 years ago

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
Helper IV
6 years ago
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!
- Anonymous
  Not applicable
  6 years ago
  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
    Helper IV
    6 years ago
    @V-pazhen-msft
    but in your example you are still using the RANKX function to find rank4value,it is not supported in DQ mode....

Related content