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

Thanks! I have highlighted in screenshot.
SchoolId, YearID, and DateValue

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