topic Re: Get Value from second highest rank per multiple dimensions in DAX Commands and Tips

Get Value from second highest rank per multiple dimensions

TOK — Tue, 22 Mar 2022 11:08:50 GMT

Hello,

I am struggeling using Rank function with multiple dimensions.

I've got three dimensions and one measure.

All I want to do is to get the second highest value (Rank = 2) grouped by all three dimensions.

It is also very unfortunate that I can't use MAXX function as it is required to be the second highest value.

Thanks for your help!

Re: Get Value from second highest rank per multiple dimensions

Jihwan_Kim — Tue, 22 Mar 2022 11:43:47 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Value total: = SUM( Data[Value] )

Rank two value: = VAR ranktwotable = FILTER ( ADDCOLUMNS ( ALLSELECTED ( Data ), "@ranking", CALCULATE ( RANKX ( ALLSELECTED ( Data ), [Value total:],, DESC ) ) ), [@ranking] = 2 ) RETURN SUMX ( ranktwotable, Data[Value] )