Comparing 2 rows in a table based on a matching data

[andy919]

Hi,

I am trying to compare 2 rows based on the value in column 1 which has a matching value in column 2 and vice versa. Column 3 tells me the number of the row for the matching pair. eg the first row shows "2" meaning the row 2 is the other pair for comparision. 

I would then like to compare the rows with the data in columns 4 and 5 based on the above criteria. 
Column 4 shows row 1 and 2 has different values so it should as a pass.

Column 5 shows a duplicate value so it should show as a fail

AAA	BBB	2	123	1q1q
BBB	AAA	1	456	1q1q

Is anyone able to help? 

Regards,

Andy

[v-rzhou-msft · Accepted solution]

Hi @andy919 ,

I think you table should look like as below.

Then create a table with column name 4 and 5. We need it to create a slicer.

Column Name = {"Column4","Column5"}

Measure:

Compare = 
VAR _SELECTVALUE =
    SELECTEDVALUE ( 'Column Name'[Column Name] )
VAR _VALUE_COLUMN4 =
    CALCULATE (
        MAX ( 'Table'[Column4] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Compare Row ID] = MAX ( 'Table'[Row ID] ) )
    )
VAR _VALUE_COLUMN5 =
    CALCULATE (
        MAX ( 'Table'[Column5] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Compare Row ID] = MAX ( 'Table'[Row ID] ) )
    )
RETURN
    SWITCH (
        _SELECTVALUE,
        "Column4", IF ( MAX ( 'Table'[Column4] ) = _VALUE_COLUMN4, "Fail", "Pass" ),
        "Column5", IF ( MAX ( 'Table'[Column5] ) = _VALUE_COLUMN5, "Fail", "Pass" )
    )

Result is as below.

Select Column4:

Select Column5:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[andy919]

Hi Rico,

Thank you. That was great help. I was able to apply this to my data which has a few thousand rows and multiple columns to compare. 
Is there a way for me to filter or export the "Fails' into another table or list?

Regards,
Andy