Ranking measure is not working

[Nnamarie_1997]

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

[v-easonf-msft]

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

[Nnamarie_1997]

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..

[v-easonf-msft · Accepted solution]

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

[Nnamarie_1997]

Hi @v-easonf-msft,

It means a lot.

Thank you so much for your help.