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.Table contains other columns as well but I need rank on three columns.

Please share any idea.

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

9 Replies

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