DAX measure for top 10 rank using Rankx function

[vijayan_ja]

Dear all,

i am looking for the output of top 10 values based on the category(Subject), The problem is i am working on the real time dataset, which dosen't allow to use any calculated column.

the output has duplicate ranks, which leads to bring more than 10 values due to ties in the value.

The formula used 

Rank = IF([FlagLatest]=1,RANKX(FILTER(ALLSELECTED(RealTimeTest),[FlagLatest]=1 && RealTimeTest[metric_name]=MAX(RealTimeTest[metric_name])),[SumwithContext],,DESC,Skip))

Please help.

[v-yulgu-msft · Accepted solution]

Hi @vijayan_ja ,

Please try this:

Rank =
IF (
    [FlagLatest] = 1,
    RANKX (
        FILTER (
            ALLSELECTED ( RealTimeTest ),
            [FlagLatest] = 1
                && RealTimeTest[metric_name] = MAX ( RealTimeTest[metric_name] )
        ),
        [SumwithContext],
        ,
        DESC,
        DENSE
    )
)

Best regards,

Yuliana Gu

[parry2k]

@vijayan_ja in this article there is a section which talks about breaking ties, scroll down the page on article and you need similar approach to make it work in your model.

[vijayan_ja]

Breaking ties using date column dosen't works, because the date value is same acrros the table. and we used Rand() but no luck.

[parry2k]

@vijayan_ja you have to break the ties somehow, why not add index column which will be unique for each record and then break ties based on that, it should be easy, no?

[vijayan_ja]

The Rand() function dosen't given the expected output.

[tex628]

Maybe you can add a super insignificant amount to your sum? 
Like:

[SumwithContext] + (RAND()/1000)

Then you rank on this measure while still displaying [SumwithContext]?

[vijayan_ja]

As i mentioned, addind index column is not possible, due to the real time dataset. 

[parry2k]

interesting, well i guess you have to somehow find a unique record, seems like that is the requirement to solve your problem. I'm surprise that random didn't worked.