Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Get Top N of a category by a value using DAX function RANKX() and a tie breaker.

I thought I was following the pattern provided in the post:  Breaking Ties in Rankings with RANKX Using Multipl... - Microsoft Power BI Community when I wrote the DAX below.  It doesn't error, and it...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    Try it.

    RankedByMoney(CareGapsToPotential) =
RANKX (
    ALL ( HN_CHIP ),
    RANKX ( ALL ( HN_CHIP ), 'HN_CHIP'[Money Left on the Table],, ASC )
        * ( COUNTROWS ( 'HN_CHIP' ) + 1 )
        + RANKX ( ALL ( HN_CHIP ), 'HN_CHIP'[Care Gaps to Potential],, DESC )
)

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    4 years ago

    I created a rank in MS SQL Server using ROW_NUMBER() OVER(PARTITION BY ... ORDER BY...)

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, Anonymous ;

Try it.

RankedByMoney(CareGapsToPotential) =
RANKX (
    ALL ( HN_CHIP ),
    RANKX ( ALL ( HN_CHIP ), 'HN_CHIP'[Money Left on the Table],, ASC )
        * ( COUNTROWS ( 'HN_CHIP' ) + 1 )
        + RANKX ( ALL ( HN_CHIP ), 'HN_CHIP'[Care Gaps to Potential],, DESC )
)

The final output is shown below:


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I created a rank in MS SQL Server using ROW_NUMBER() OVER(PARTITION BY ... ORDER BY...)