Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Rank based on three fields

I tried to write DAX expression in TOM Model to get the rank based on three categories but didn't got solution. Ex: There are 2 schools and each schools have different year and each year have date.T...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI Anonymous,

     

    According to your description and snapshot, you want to rank datevalue based on current 'schoolid' and 'yearid' category, right?

    If this is a case, you can try to use following calculated column formula:

    RANK =
COUNTROWS (
    FILTER (
        ALL ( Table ),
        Table[SchoolId] = EARLIER ( Table[SchoolId] )
            && [YearId] = EARLIER ( Table[YearId] )
            && Table[DateValue] < EARLIER ( Table[DateValue] )
    )
)
    + 1

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hello Anonymous,

pls send me the output you want for your data pls just type it in excel and send me that, so i can understand what you exactly want.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

 

I'm looking for RANK field which is in attached screenshot.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Anonymous,

     

    According to your description and snapshot, you want to rank datevalue based on current 'schoolid' and 'yearid' category, right?

    If this is a case, you can try to use following calculated column formula:

    RANK =
COUNTROWS (
    FILTER (
        ALL ( Table ),
        Table[SchoolId] = EARLIER ( Table[SchoolId] )
            && [YearId] = EARLIER ( Table[YearId] )
            && Table[DateValue] < EARLIER ( Table[DateValue] )
    )
)
    + 1

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hello Anonymous,

    hope you Have you got your solution. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks! AnonymousAbove DAX expression is helped me.
      Thanks! Anonymous