Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rank on multipl dates

Hi,

 

Need help in tweaking this DAX to accomodate one more date - Sent Date.

So it should rank dynamically based on Submit date as well as Sent date

 

RANK3 = RANKX (
    FILTER ( ALLSELECTED (Table1), Table1[FullName] = SELECTEDVALUE ( Table1[FullName] ) ),
    CALCULATE ( SELECTEDVALUE ( Table1[SubmitDate] )),,DESC,DENSE )

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous ,

    Do you mean rank by multiple columns? If this is a case, I'd like to suggest you use ISONORAFTER function: 

    RANK3 =
COUNTROWS (
    FILTER (
        ALLSELECTED ( Table1 ),
        Table1[FullName] = SELECTEDVALUE ( Table1[FullName] )
            && ISONORAFTER (
                    Table[SubmitDate], MAX ( Table[SubmitDate] ), ASC,
                    Table[SentDate], MAX ( Table[SentDate] ), ASC
            )
    )
)

    Reference link:

    Sorting by multiple columns 

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    if your data is not confidential please share the sample data and expected output.

    Best Regards,
    Mail2inba4

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

    Do you mean rank by multiple columns? If this is a case, I'd like to suggest you use ISONORAFTER function: 

    RANK3 =
COUNTROWS (
    FILTER (
        ALLSELECTED ( Table1 ),
        Table1[FullName] = SELECTEDVALUE ( Table1[FullName] )
            && ISONORAFTER (
                    Table[SubmitDate], MAX ( Table[SubmitDate] ), ASC,
                    Table[SentDate], MAX ( Table[SentDate] ), ASC
            )
    )
)

    Reference link:

    Sorting by multiple columns 

    Regards,

    Xiaoxin Sheng