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