Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Rank in direct query mode

Hi,   How would I go about ranking values in direct query mode.  The DAX function RANK does not seem to work.   Any help appreciated   TIA   Chris
Need Help
  • hemanth's avatar
    hemanth
    9 years ago

    Looks like it is not supported . It should get added as a feature

ukeasyproj's avatar
ukeasyproj
Icon for Helper II rankHelper II
8 years ago

I faced this a couple of months ago

 

two ways to do this, you can create a Measure using rankx 

 

this means that when you slice your data, your rankings get recalculated

 

another option is to do the rank within the SQL select query

 

this means your ranking remains the same regardless of how you slice your data

 

this will show you how to do it the SQL way: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109028

 

 

pandakillsalot's avatar
pandakillsalot
Icon for Helper II rankHelper II
8 years ago

Thank you so much for your reply. I tried the RANKX way but I got an error saying the RankX function is not supported under direct query model. How did you solve it? By rankx or SQL ? 

  • ukeasyproj's avatar
    ukeasyproj
    Icon for Helper II rankHelper II
    8 years ago

    go to file->options and settings -> options

     

    under direct query, select "allow unrestricted measures"

    • pandakillsalot's avatar
      pandakillsalot
      Icon for Helper II rankHelper II
      8 years ago

      That's a right way to do it. However, all DAX functions are only allowed to apply in measures. I want to add each serial number 's rank as a new column as by comparing their timestamp and then focus on test data from the same rank, like how many failures do I got on the first run. I don't really think I could handle that with one single measure.