Forum Discussion

NilR's avatar
NilR
Post Patron
3 years ago
Solved

Ranking in Power Query

I have Dax Rankx and would like to convert into M query ( power Query), is it possible?

 

 

 

Ranking Measure=
var _end_date = 'Table1'[Start_Date2]
var _start_date = EOMONTH(_end_date,-24)+1

VAR _COUNT = RANKX ( 
        FILTER (ALL('Table1'),
            'Table1'[GP] = EARLIER('Table1'[GP])
            && 'Table1'[INDV_ID] = EARLIER('Table1'[INDV_ID])
            && 'Table1'[MALES_UNDER_35] = EARLIER('Table1'[MALES_UNDER_35])
            && 'Table1'[Start_Date2] >= _start_date
            && 'Table1'[Start_Date2] <= _end_date
        ),        
        'Table1'[Start_Date2],
        ,
        ASC,
        Dense
    )
 RETURN 
 
_COUNT

 

 

Need Help

4 Replies

    • NilR's avatar
      NilR
      Post Patron
      3 years ago

      I ended up using this technic since nothing else is working but I also added below line to my sql:

      DENSE_RANK() OVER (PARTITION BY GP, INDV_ID,MALES_Under_35 ORDER BY YRMO ASC ) AS Active_MOS

       

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

    Examine the recently added function:  Table.AddRankColumn.  Has a variety of arguments to handle ties.

    • NilR's avatar
      NilR
      Post Patron
      3 years ago

      Thank you! I am using corp version and it's not updated yet :'( Otherwise this is exactly what I needed!!