A simple solution to deal with ties RANKX (DAX)

SQL has a ROW_NUMBER function that will generate unique number even if the expression in the order by clause has a tie.

This is not possible in DAX as RANKX cannot generate unique number if there is a tie therefore, we need to introduce some randomness to our expression

Also, that the Top N or Bottom N visual filter may return N+ 1 element if there is a tie. Here is the problem

I have a products table; I want to rank products from lowest online sales to highest. Here is the Bottom 8

As you can see Bottom 8 returns 9 items due to tie issue

The other issue with visual filter is if I add category to the table/matrix. I do not get Top N per category i.e., in this case Bottom 8 per category

Definition of Rank Products Per Sales measure used in the above expression is (In this writeup I am using RANKX in a measure and not calculated column)

Rank Products By Sales =
IF (
    ISINSCOPE ( Products[Product Name] ),
    VAR rankasc =
        RANKX (
            CALCULATETABLE (
                VALUES ( Products[Product Name] ),
                FILTER ( ALLSELECTED ( Products[Product Name] ), [Sales] <> BLANK () )
            ),
            [Sales],
            ,
            ASC,
            DENSE
        )
    RETURN
        IF ( [Sales] <> BLANK ()rankasc )

To resolve the tie issue

One solution usually is that if there is an alternate measure that can be used as an expression, we can add this alternate measure to existing expression for randomness

Something like this

RANKX(<Table> , [Measure] + [Alternate Measure]/Filtered Grand Total of Alternate Measure ,,ASC,DENSE)

but again, this does not guarantee uniqueness as there can be products with same measure value and alternate measure value

Here is the solution to deal with ties which guarantees uniqueness

If expression in RANKX is a whole number use this

Rank Product By Sales Unique 2 =
IF (
    ISINSCOPE ( Products[Product Name] ),
    VAR rankasc =
        RANKX (
            CALCULATETABLE (
                VALUES ( Products[Product Name] ),
                FILTER ( ALLSELECTED ( Products[Product Name] ), [Sales] <> BLANK () )
            ),
            [Sales]
                + (
                    1
                        / (
                            10
                                RANKX (
                                    FILTER ( ALLSELECTED ( Products[Product Name] ), [Sales] <> BLANK () ),
                                    FIRSTNONBLANK ( Products[Product Name], 1 ),
                                    ,
                                    ASC,
                                    DENSE
                                )
                        )
                ),
            ,
            ASC,
            DENSE
        )
    RETURN
        IF ( [Sales] <> BLANK ()rankasc )
)

If expression in RANKX has decimal values then use this

Rank Product By Sales Unique =
IF (
    ISINSCOPE ( Products[Product Name] ),
    VAR rankasc =
        RANKX (
            CALCULATETABLE (
                VALUES ( Products[Product Name] ),
                FILTER ( ALLSELECTED ( Products[Product Name] ), [Sales] <> BLANK () )
            ),
            [Rank Products By Sales]
                + (
                    1
                        / (
                            10
                                RANKX (
                                    FILTER ( ALLSELECTED ( Products[Product Name] ), [Sales] <> BLANK () ),
                                    FIRSTNONBLANK ( Products[Product Name], 1 ),
                                    ,
                                    ASC,
                                    DENSE
                                )
                        )
                ),
            ,
            ASC,
            DENSE
        )
    RETURN
        IF ( [Sales] <> BLANK ()rankasc )
)

OR

Rank Product By Sales Unique 1 =
IF (
    ISINSCOPE ( Products[Product Name] ),
    VAR rankasc =
        RANKX (
            CALCULATETABLE (
                VALUES ( Products[Product Name] ),
                FILTER ( ALLSELECTED ( Products[Product Name] ), [Sales] <> BLANK () )
            ),
            [Rank Products By Sales]
                + (
                    RANKX (
                        FILTER ( ALLSELECTED ( Products[Product Name] ), [Sales] <> BLANK () ),
                        FIRSTNONBLANK ( Products[Product Name], 1 ),
                        ,
                        ASC,
                        DENSE
                    )
                )
                    CALCULATE ( COUNTROWS ( Products )ALLSELECTED ( Products[Product Name] ) ),
            ,
            ASC,
            DENSE
        )
    RETURN
        IF ( [Sales] <> BLANK ()rankasc )
)

Bottom 8 now looks like this


Note that for tied products “Rank Product
By Sales Unique 1″ sorts product in an ascending order and

“Rank Product
By Sales Unique” sorts product in a descending order

If we use visual filter “Rank Product
By Sales Unique” <= 8 instead of TopN we correctly get 8 items instead of 9


If we add category, we get 8 unique items per category