Rankx tiebreaker

[danning1234]

Hello,

I have a data table, want to rank the [ExceptionTitle] by [ServiceRequestDistNb] regardless the tie by [RequestType], when respecting the [RequestTypeSort] order.

Example, "Orig" should be before "Admin" for always in the rank result. Base on this, rank by measure [ServiceRequestDistNb]. That means, the rank by value is the total effect of [RequestType] ASC and [ServiceRequestDistNb] DESC.

The number in red is the wanted result.

I have already a measure [RankByValue]. How to rank the table by this value? 

Here is the link to the sample file.

https://www.dropbox.com/s/mancesu0g7pe4jo/Rankx.pbix?dl=0

Thank you.

[Vera_33 · Accepted solution]

Hi @danning1234 

have a try

RankByValue = 
  SELECTEDVALUE( 'Query1'[RequestTypeSort] )  * 1000
        + RANKX (
    ALLSELECTED(  'Query1'[ExceptionTitle] ),
    [ServiceRequestDistNb],
    ,
    DESC,
    DENSE
)


Test4- rank = 
RANKX (
       ALLSELECTED(  'Query1'[ExceptionTitle],  'Query1'[RequestType] ,'Query1'[RequestTypeSort],Query1[FiscalYear]),
[RankByValue],,ASC,DENSE
)