Rank based on three fields

[Anonymous]

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.

[Anonymous]

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

Thanks.

[Anonymous]

[Anonymous]

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]

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

[Anonymous]

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]

I'm looking for RANK field which is in attached screenshot.

[Anonymous]

Hello @Anonymous,

hope you Have you got your solution. 

[Anonymous]

Thanks! @AnonymousAbove DAX expression is helped me.
Thanks! @Anonymous

[Anonymous · Accepted solution]

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