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,
pls tell me the field name on which you want to rank.

Ex. Strength of student in schools, Achievement of schools etc, 

so on what basis you want a rank ?

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