Rank values stored in a list (PathValue)

[johnyip]

Hello there, I am here to ask some weird question again.

I have created a "list", which can be passed into the IN opeartor, as the below hyperlink:

Solved: Pass the selected values of a slicer into the IN o... - Microsoft Power BI Community

My question is, how  to rank the values of such a list?

For example, if the list is {46,283,133,233,34}, I would like to rank it as {2,5,3,4,1}.

I hope I can achieve tat by using some DAX in a measure, because the "list" is generated in a measure, and I need to retrieve the ranked value and pass it into the measure.

Thanks a lot.

[amitchandak]

@johnyip , I think concatenatex on the rank measures with values should give you that

concatenatex(Values(Table[Column]), [Rank])

But I doubt the order. May you have build both using same order column in concatenatex

[johnyip]

@amitchandak , I am using that "list"  to select records of some companies with the ranks specified in that "list".

After this selection, I wish to rank all those selected companies' ranks based on their actual rankings (that is the value in that list), and then map this new ranking to the respective selected companies in a measure.

Any ideas on that? Because the data is in a summarized table and I need to doall these tricks to make slicers in the report working.

[johnyip · Accepted solution]

I figured out the solution myself.

It is to use the same trick as this link to generate a lookuptable with both old rank and new rank using SELECTCOLUMNS(), like this:

VAR LookupList =

SELECTCOLUMNS(

GENERATESERIES ( 1, PATHLENGTH ( PathVar2 ) ),

"Old Rank", VALUE(PATHITEM ( PathVar1, [Value])),

"New Rank", VALUE(PATHITEM ( PathVar2, [Value] ))

)

And then finally return:

MAXX(FILTER(LookupList,[Old Rank] IN {MAX('Table'[Column])}),[New Rank])

Tricky enough though.

I have take this link regarding mimiking loops in DAX. I also put it here for reference.

For and While Loops in DAX - Microsoft Power BI Community