Need help on RANKX function

[admin_xlsior]

Hello all,

Need help on RANKX. 

I have this measure :

Rank turnover item high = 
VAR temp = [Turnover days]
RETURN IF(
            temp = 0, BLANK(),
            RANKX(
                    ALL('Products'[Product number]),
                    CALCULATE([Turnover days]),,ASC,Dense
                )
)

which currently result this table

If you see, there is missing Rank #2. This is because I have condition if the value is 0 then put Blank. Because if I didn't put that condition, there will be a lot of 0 values which result more "ugly" ranking. 

But still, can the ranking not skip like that figure ?

Thanks

[mahoneypat · Accepted solution]

I would stick with the simpler approach.  If some product numbers have the same length you could use the UNICODE of the last character instead.  Or you could use both like below.

Rank turnover item high =
VAR temp = [Turnover days]
RETURN
    IF (
        temp = 0,
        BLANK (),
        RANKX (
            FILTER (
                ALL ( 'Products'[Product number] ),
                [Turnover days] <> 0
            ),
            VAR thisPN =
                CALCULATE (
                    MIN ( 'Products'[Product number] )
                )
            RETURN
                [Turnover days]
                    + (
                        LEN ( thisPN )
                            + UNICODE (
                                RIGHT (
                                    thisPN,
                                    1
                                )
                            )
                    ) / 10000,
            ,
            ASC,
            DENSE
        )
    )

Regards,

Pat

[admin_xlsior]

Hi @mahoneypat 

Finally it works! Although I'm not too sure I understand this 😅

Especially starting with "inner" VAR and RETURN. I didn't know it even allowed to do that, but it is.

MANY THANKS @mahoneypat  Bless you.😇

rgds,

[mahoneypat]

That would work, but I think we should just fixe one of your earlier/simpler ones.  The one using the length of the product number that gave the error.

Rank turnover item high =
VAR temp = [Turnover days]
RETURN
    IF (
        temp = 0,
        BLANK (),
        RANKX (
            FILTER (
                ALL ( 'Products'[Product number] ),
                [Turnover days] <> 0
            ),
            [Turnover days]
                + CALCULATE (
                    LEN (
                        MIN ( 'Products'[Product number] )
                    )
                ) / 1000,
            ,
            ASC,
            DENSE
        )
    )

Regards,

Pat

[admin_xlsior]

Hi @mahoneypat 

Sorry to say, it didn't hit the target.

Then I tried to create what has been taught in that Youtube.  Actually in table looks good on the 2 steps, but weird is on the 3rd step which is actually not ranking the rank with decimal,  is false. although it looks everything has no duplicate value.

Here are what I do:

1. Ranking the "Turnover days", and get the rank scale which when I make it 4 decimal looks good.

Rank product tiebreaker = 
VAR tmp = [Turnover days]
VAR tmpRank = IF(
                tmp <> 0,
                RANKX(
                      FILTER(
                            ALL('Products'[Product number]),
                            [Turnover days] <> 0),
                       [Turnover days] ,,DESC,Dense
                ),
                BLANK()
                )

VAR RankScale = DIVIDE(tmpRank + RAND(),100)

 RETURN IF( 
            tmp <> 0,
            RankScale,
            BLANK()
 )

2.  Add my Turnover days to this decimal form point 1.

Turnover days tiebreaker = [Turnover days] + [Rank product tiebreaker]

3. Rank the result 

Rankx Contest Final = 
VAR temp = [Turnover days tiebreaker]
RETURN IF(
            temp <> 0,             
            RANKX(
                FILTER(
                        ALL('Products'[Product number]),
                        [Turnover days tiebreaker] <> 0),
                [Turnover days tiebreaker],,
                DESC,
                Dense
            ),
            BLANK()
)

The result in table, for up to no 2 is good, no duplicate but still on point 3, some ranking skip and duplicate. Also there is some weird adding like below:

Turnover days tiebreaker (like in point 2) is actually only adding Turnover days with Rank product tie breaker, but I don't  know why, the result (turnover days tiebreaker) were incorrectly added, some cross items, some were just don;t know where it is coming from

But anyhow, the value all is different (Turnover days tiebreaker), but still after point 3, ranking all those different value, the result (rank contest final) produced duplicate rank and some skipped.

Thanks,

[mahoneypat]

I added more rows to my sample data and reproduced the same behavior.  I think it is using the same RAND number on each row for efficient when calculation the variable table.  To get around it, I added a calculated column to the table (in your case the Product table with a random number, and then used it in the new measure.  See below.

NewMeasures =
VAR vProduct =
    MIN ( 'Products'[Product number] )
VAR vRankTable =
    ADDCOLUMNS (
        FILTER (
            ALL ( 'Products'[Product number] ),
            [Turnover days] <> 0
        ),
        "Product",
            [Turnover days]
                + CALCULATE (
                    MIN ( 'Products'[RandNum] )
                ) / 10000
    )
VAR vThisValue =
    MINX (
        FILTER (
            vRankTable,
            'Products'[Product number] = vProduct
        ),
        [Product]
    )
RETURN
    RANKX (
        vRankTable,
        [Product],
        vThisValue,
        DESC,
        DENSE
    )

Regards,

Pat

[admin_xlsior]

Hi @mahoneypat 

I think this is make more uncertain of the ranking. I tried in mine, it is still produce the duplicate ranking. Maybe because I didn't have "numeric product number" which can give me a big numbers. What I did is by using the length again to have some numbers but then again it could not give me a big numbers. I think the key to have "good" random number is by having a big number (10 digits) and divide by some random big number again instead using RAND() which still produce same value.

Also, some weird behaviour is that it doesn;t filter Turnover days = 0. which I don't understand because in vRankTable I have specified Turnover days <> 0. 

Recenlty I found this YouTube Developing Custom Tiebreakers in RANKX - DAX Tutorial - YouTube 

Although not really give details, but I think I like the idea of having 2 level ranking that the decimal (random by calculation) is added to the 1st rank instead the value that we're using for the "base" ranking, like below :

1. Rank first

2. Add the random decimal to the rank with the same value (actually it is added all the rank, but the intention is the rank with same value)

3. Rank the rank but this time all the ranking already has addition of decimal number including the one with same value.

I still figure out how to do that unfortunately.

Thanks

[mahoneypat]

Your idea to use RAND() in the RANKX is a good one.  I played with that too, and it is showing weird behavior.  Two suggestions - 

1. You can add RAND() to the rest of the RANKX() but then you don't get integer ranks

2.  Use this approach to get the rank (makes a virtual table with values + RAND, filters to find the new value + RAND, then uses it in the 3rd term of RANKX.  Adapt it for your table and column names.

Rank2 =
VAR vThisCat =
    MIN ( RankTest[Category] )
VAR vRankTable =
    ADDCOLUMNS (
        ALL ( RankTest[Category] ),
        "cMin",
            [MinValue]
                + RAND () / 10000
    )
VAR vThisValue =
    MINX (
        FILTER (
            vRankTable,
            RankTest[Category] = vThisCat
        ),
        [cMin]
    )
RETURN
    RANKX (
        vRankTable,
        [cMin],
        vThisValue,
        DESC,
        DENSE
    )

Regards,

Pat

[admin_xlsior]

Hi @mahoneypat 

Hope I following your approach right. so this is the code

Measure 3 = 
VAR vProduct = MIN('Products'[Product number])
VAR vRankTable =
    ADDCOLUMNS(
                FILTER(ALL('Products'[Product number]),
                            [Turnover days] <> 0)
                , "Product",
                [Turnover days] + RAND()/10000
    )
VAR vThisValue =
    MINX(
        FILTER(
                vRankTable,
                'Products'[Product number] = vProduct
        ), [Product]
    )

RETURN RANKX(
            vRankTable, 
            [Product],
            vThisValue,DESC, Dense
)

I think it is getting closer, but still duplicate if more than 2. is it something got to do with the MINX() ?

Thanks

[mahoneypat]

It depends on how you want to differentiate them.  Here is one way where you use the last # of the product # (divided by a large number to avoid changing the main rank.

Rank turnover item high =
VAR temp = [Turnover days]
RETURN
    IF (
        temp = 0,
        BLANK (),
        RANKX (
            FILTER (
                ALL ( 'Products'[Product number] ),
                [Turnover Days] <> 0
            ),
            [Turnover days] + VALUE(RIGHT('Products'[Product number], 1))/1000000000,
            ,
            ASC,
            DENSE
        )
    )

Regards,

Pat

[admin_xlsior]

Hi @mahoneypat 

I like the idea, but I don't know why in my code, it is saying "a single value for column "Product number" in table "Product" cannot be determined". Is the formula returns multple rows ?

Tried to use RAND() as well for the Tie Breaker, to avoid the use of Product number but still not resolve the "same rank" problem.

Btw, my product number is unfortunately not a whole number and not always end with number so I tried to use something else like count the length of the Product number then divide by 1000 (just to get a random small number) :

Rank turnover item high = 
VAR temp = [Turnover days]
RETURN IF(
            temp = 0, BLANK(),
            RANKX(
                    FILTER(
                            ALL('Products'[Product number]),
                            [Turnover days] <> 0
                    ),
                    [Turnover days] + LEN('Products'[Product number])/1000 ,,ASC,Dense 
                )
)

There's when I get this message :

Clearly it's not because the "LEN" function but relate to the Product number it self which contain multiple records.

Thanks.

---

[mahoneypat]

You can try adding a FILTER around your ALL.  Note this can be written more efficiently by making a virtual table in a variable first.

Rank turnover item high =
VAR temp = [Turnover days]
RETURN
    IF (
        temp = 0,
        BLANK (),
        RANKX (
            FILTER (
                ALL ( 'Products'[Product number] ),
                [Turnover Days] <> 0
            ),
            [Turnover days],
            ,
            ASC,
            DENSE
        )
    )

Regards,

Pat

[admin_xlsior]

Hi @mahoneypat ,

Thank you very much. It's works!.

There is one last thing though, if we facing a same value, is there some trick to avoid same ranking ?

I tried the code just now, it works for the first measure which using ASCENDING, but it turn out when I use it for the DESCENDING, I hit 2 rows (product) that having exactly the same figures.

If you see in the picture, the one on the left is correct, but on the right I have 2 rows of rank #2. As I tried to increase the decimal, it is reveal that the 2 items realy having the exact value even until 10 decimal.

Much appreciated if there is some advice on what should we do if we encounter such issue on RANK.

Thanks in advance