Forum Discussion

laciodrom_80's avatar
laciodrom_80
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...
Need Help
Tutorial Requests
laciodrom_80's avatar
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's avatar
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's avatar
    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....

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        laciodrom_80
        Helper IV
        6 years ago

        Anonymous 

         

        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