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

pls share sample data structure of your database , mainly that three column should would availble in data structure.

Thanks.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

 

 

  • 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.