Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Rank

 

Hi,

 

I have a table, Table1, and I want to rank multiple meters by End date in the following format.

 

Can you help?

 

Meter              End Date     Rank
1000364810   2008 06        1
1000364810   2009 06        2
1000364810   2012 06        3
1000364810   2015 06        4
1000364810   2020 04        5

 

Many thanks in advance!

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi PMF99 

     

    please see the below

    =VAR m = Resign_Append[meter]
RETURN
RANKX(
FILTER(Resign_Append,
Resign_Append[meter] = m
),
Resign_Append[CED YYYY MM],,
ASC
)

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous,

    If 'End Date' is of type date, you can simply do:

    Rank_Column =
RANKX ( CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Meter])), Table1[End Date], Table1[End Date], ASC )
  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous,

    You can add a column and the code below.

    Rank Meter by End Date = 
VAR m = Table1[Meter]
RETURN 
RANKX(
    FILTER(
        Table1,
        Table1[Meter] = m
    ), 
   Table1[End Date],,
   ASC
)


    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • PMF99's avatar
      PMF99
      Regular Visitor
      7 years ago

      Many thanks Mariusz;

      I tried your code;

       

      =VAR m = Resign_Append[Renewal Date]
      RETURN
      RANKX(
      FILTER(Resign_Append,
      Resign_Append[CED YYYY MM] = m
      ),
      Resign_Append[CED YYYY MM],,
      ASC
      )

       

      And here's the error I've got;

       

      DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

       

      What have I done wrong?

       

      Thanks

      Peter

       

       

       

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi PMF99,

        Try,

        =VAR m = Resign_Append[Renewal Date]
RETURN
RANKX(
FILTER(Resign_Append,
Resign_Append[Renewal Date] = m
),
Resign_Append[CED YYYY MM],,
ASC
)

        Regards,
        Mariusz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.