Comparing values of the same column followed by comparing with different column to create output.

[Anonymous]

Hello,
I am trying to create a new column "Result".
The Result column is created based on this logic, compare value on B3 with value on B2, if it is the same then compare value on A3 with value on A2 if it is the same then the Result in C3 = 1. 
Next, compare value on B4 with value on B3, if it is the same then compare value on A4 with value on A3 if it is different then the Result in C4 = 2.
Finally, compare value on B5 with value on B4, if it is different then the Result in C5 = 3.

Thank you in advance.

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could try this way:

Step1:

Add an index column in edit queries or use Rankx Function

Step2:

Use this formula to add a calculate column

Result = 
IF (
    'Table'[Index] = 1,
    "s",
    IF (
        CALCULATE (
            MAX ( 'Table'[Type] ),
            FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
        ) = 'Table'[Type],
        IF (
            CALCULATE (
                MAX ( 'Table'[Size] ),
                FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
            ) = 'Table'[Size],
            "1",
            "2"
        ),
        "3"
    )
)

Result:

here is a simple pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Is the data in any order? Can you rank them?