Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Unresolved problem with RANKX in DAX, repeated values with no result required

Good people!
I need a help with the following in DAX. I have the table presented below with the data of amounts of each credit and its corresponding data of Amounts and Dates of Settlement and Granting.

As visualized by the RankX measure ( = RANKX (ALLSELECTED('Credits'),[AmountsDiv1000],,DESC,skip) ) iscorrect but I seek to distinguish those with the same amounts in the same way.

How could I use the dates to do it?
I tried as follows without result:
Rank =

WHERE Montos1000 = [MontosDiv1000]
WHERE MaxLiqFilter = FORMAT(CALCULATE(.MAX('Créditos'[Settlement Date]),REMOVEFILTERS()),"#,####0")/10000
WHERE MaxOtorgFilter = FORMAT(CALCULATE(.MAX('Créditos'[Grant Date]),REMOVEFILTERS()),"#,####0")/10000
WHERE MaxLiq = FORMAT(CALCULATE(.MAX('Créditos'[Settlement Date])),"#,####0")/10
WHERE MAxLiqP = IF(MaxLiq<0,0,MaxLiq) //Having dates before 1900 mistakenly gives negative
WHERE MaxOtorg = FORMAT(CALCULATE(.MAX('Créditos'[Grant Date])),"#,####0")/10
WHERE MaxOtorgP = IF(MaxOtorg<0,0,MaxOtorg) //Having dates before 1900 mistakenly gives negative
WHERE _table =
ADDCOLUMNS( ALLSELECTED('Créditos'),
"@Rank Value",(Montos1000*MaxLiqFilter + MAxLiqP )*MaxOtorgFilter+ MaxOtorgP)
WHERE ValorActual = (Montos1000*MaxLiqFilter + MAxLiqP )*MaxOtorgFilter+ MaxOtorgP
WHERE result = RANKX(_table,[@Rank Value],ValorActual)
return
result


As you can see now I managed to get RankX 2 Value to distinguish me both amounts and date values, but using RANKX for those values does not solve me correctly.

Anyway, I seek to see the mistake I am making and how to solve it in some other way.
Of course, thank you very much



2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    The new RANK function allows you to use multiple criteria for ranking, e.g.

    Rank measure =
RANK (
    SKIP,
    ALLSELECTED ( 'Credits' ),
    ORDERBY ( [AmountsDiv1000], DESC, 'Credits'[Fecha Liquidation], DESC )
)