topic Re: Ranking measure is not working in DAX Commands and Tips

Ranking measure is not working

Nnamarie_1997 — Wed, 15 Jun 2022 01:11:58 GMT

Hi, All.

Can you help me with this?

I have this kind of table that consist of location, item, status, score, & target.

The first thing I did was to Identified the Scores based on Status using this condition:

1Score2: =

VAR target = CALCULATE(SUMMARIZE('Table','Table'[TARGET]))

RETURN

SWITCH(SELECTEDVALUE('Table'[STATUS]),

"NEGATIVE", CALCULATE(MIN('Table'[SCORE]), 'Table', 'Table'[SCORE] < 0),

"LOW", CALCULATE(MIN('Table'[SCORE]), 'Table', 'Table'[SCORE] < target),

"HIGH", CALCULATE(MAX('Table'[SCORE]), 'Table', 'Table'[SCORE] > target),

"NORMAL", CALCULATE(MAX('Table'[SCORE]))

)

And I get this result

Now, I want two rank it based on the scores on each status. Using this measure:

1Rank =
VAR StatusType = SELECTEDVALUE('Table'[STATUS])
RETURN
SWITCH(TRUE(),
StatusType = "NEGATIVE", RANKX(ALL('Table'[ITEM]),[1Score2:],,ASC, DENSE),
StatusType= "LOW", RANKX(ALL('Table'[ITEM]),[1Score2:],,ASC, DENSE),
StatusType = "HIGH", RANKX(ALL('Table'[ITEM]),[1Score2:],,DESC, DENSE),
StatusType = "NORMAL", RANKX(ALL('Table'[ITEM]),[1Score2:],,DESC, DENSE)
)

Then, I get this result

The measure, works fine for the two country.

But to this country, the rank is not working under Status: Low .

Here is my expected Result:

Cases:

If the Status is "NEGATIVE" or "LOW", the Rank must start in the lowest value.

If the Status is "NORMAL" or HIGH, the Rank must start in the highest value.

See Examples Below:

Thanks in Advance.

Please refer to this link for my sample files:

https://drive.google.com/drive/folders/1jDjGSM9fxMoXg3hxIiNIaQQRbBb54Euj

Re: Ranking measure is not working

v-easonf-msft — Mon, 13 Jun 2022 08:56:52 GMT

Hi, Nnamarie_1997

Not quite understand what you mean. From your formula, the value of rank should depend on the value of [1Score2:].

What is your expected result?

Best Regards,
Community Support Team _ Eason

Re: Ranking measure is not working

Nnamarie_1997 — Wed, 15 Jun 2022 01:13:19 GMT

Hi @v-easonf-msft ,

Sorry for Late Reply.

Here is my expected result.

Cases:

If the Status is "NEGATIVE" or "LOW", the Rank must start in the lowest value.

If the Status is "NORMAL" or HIGH, the Rank must start in the highest value.

See Examples Below:

Thank You..

Re: Ranking measure is not working

v-easonf-msft — Wed, 15 Jun 2022 07:45:39 GMT

Hi, Nnamarie_1997

If you need to rank by measure [1Score2: ],please try measure like:

Rank(1Score2) = VAR tab = ADDCOLUMNS ( SUMMARIZE ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LOCATION] = MAX ( 'Table'[LOCATION] ) && 'Table'[STATUS] = MAX ( 'Table'[STATUS] ) ), 'Table'[LOCATION], 'Table'[ITEM], 'Table'[STATUS] ), "@1Score2", [1Score2:] ) VAR CurrentValue = [1Score2:] RETURN SWITCH ( TRUE (), SELECTEDVALUE ( 'Table'[STATUS] ) IN { "NEGATIVE", "LOW" }, RANKX ( tab, [@1Score2], CurrentValue, ASC, DENSE ), SELECTEDVALUE ( 'Table'[STATUS] ) IN { "Normal", "High" }, RANKX ( tab, [@1Score2], CurrentValue, DESC, DENSE ) )

If you need to rank by field Table[SCORE],try measure like:

Rank(score) = VAR tab = FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LOCATION] = MAX ( 'Table'[LOCATION] ) && 'Table'[STATUS] = MAX ( 'Table'[STATUS] ) ) RETURN SWITCH ( TRUE (), SELECTEDVALUE( 'Table'[STATUS] ) IN { "NEGATIVE", "LOW" }, RANKX ( tab, CALCULATE ( MAX ( 'Table'[SCORE] ) ),, ASC, DENSE ), SELECTEDVALUE('Table'[STATUS] ) IN { "Normal", "High" }, RANKX ( tab, CALCULATE ( MAX ( 'Table'[SCORE] ) ),, DESC, DENSE ) )

Best Regards,
Community Support Team _ Eason

Re: Ranking measure is not working

Nnamarie_1997 — Wed, 15 Jun 2022 08:22:17 GMT

Hi v-easonf-msft,

It means a lot.

Thank you so much for your help.