Determine if value is greater between a list of numbers

[Anonymous]

Currently trying to determine the best way to find out if other values in a list are greater than a declared variable.

For example, we have 2020 Q2 in the database set as 202002 (a numeric value), this is followed by 202001, 201904, 201903, and so on. These all have differeing values, and I want to be able to check if the value for 202002 is higher than those before. The data I have looks like this:

There are four items being pulled through, the rows are RecordID & Quarter while the column is Name and the values being pulled through are StandardResult. You can see here that 202002 is not the highest, and 201801 is - I've highlighted this as blue to show how the anticipated end-result should work.

I've tried to write something with what knowledge I have but it doesn't seem to resolve. Is there a way to take the numeric value of 202002, set it as a variable to check all other values in this list? Is there a way to make a list of items, excluding 202002 and checking all other values against this?

[Fowmy · Accepted solution]

@Anonymous 

You can add this measure to identify the highest in the range

M = 
IF(
    SELECTEDVALUE(Table3[RESULT]) = 
    CALCULATE(
        MAX(Table3[RESULT]),
        ALLSELECTED(Table3[QTR])
    ),
    1,
    0
)

________________________

If my answer was helpful, please mark this post as a solution, this will also help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube
LinkedIn

[Anonymous · Accepted solution]

Hi @Fowmy, thanks for your message. I've taken the information you have passed over but ran into a snag. It shows a 1 value against all of the results, shown below:

It is possible I've made an error in the code somewhere. The 1.00 & 2.00 values are the StandardResults, the Quarter is 202001, 02, etc. I've moved these around a fair bit to see if I can fix it but it either shows them all as 1 or all as 0, whereby the only one that should show as a 1 is the orange highlighted field.

CheckHighestInRange = 
IF (
    SELECTEDVALUE ( ALLUNITS[StandardResult] )
        = CALCULATE (
            MAX ( ALLUNITS[StandardResult] ),
            ALLSELECTED ( ALLUNITS[Quarter] )
        ),
    1,
    0
)

 Can you see where my error is with this?

[Fowmy · Accepted solution]

@Anonymous 

You can add this measure to identify the highest in the range

M = 
IF(
    SELECTEDVALUE(Table3[RESULT]) = 
    CALCULATE(
        MAX(Table3[RESULT]),
        ALLSELECTED(Table3[QTR])
    ),
    1,
    0
)

________________________

If my answer was helpful, please mark this post as a solution, this will also help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube
LinkedIn

[Anonymous · Accepted solution]

Hi @Fowmy, thanks for your message. I've taken the information you have passed over but ran into a snag. It shows a 1 value against all of the results, shown below:

It is possible I've made an error in the code somewhere. The 1.00 & 2.00 values are the StandardResults, the Quarter is 202001, 02, etc. I've moved these around a fair bit to see if I can fix it but it either shows them all as 1 or all as 0, whereby the only one that should show as a 1 is the orange highlighted field.

CheckHighestInRange = 
IF (
    SELECTEDVALUE ( ALLUNITS[StandardResult] )
        = CALCULATE (
            MAX ( ALLUNITS[StandardResult] ),
            ALLSELECTED ( ALLUNITS[Quarter] )
        ),
    1,
    0
)

 Can you see where my error is with this?

[Fowmy]

@Anonymous 

Try that one as a measure.

If you need to add as a column then,

CheckHighestInRange Col = 
IF (
     [RESULT] 
        = CALCULATE (
            MAX ( Table3[RESULT] ),
            ALLSELECTED (Table3[QTR],Table3[RESULT] )
        ),
    1,
    0
)

________________________

If my answer was helpful, please mark this post as a solution, this will also help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube
LinkedIn

[Anonymous]

@Fowmy 

What do you mean sorry? This is saved as a measure already, am I misunderstanding this?

[Fowmy · Accepted solution]

@Anonymous 

Check the attached file and compare it with yours.
You can download the file: HERE

________________________

If my answer was helpful, please mark this post as a solution, this will also help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube
LinkedIn

[Anonymous]

Thanks @Fowmy it looks to be data related in my instance, I can see yours works as expected so I will need to take a look at the information my report is pulling through.