topic Re: Comparing 2 rows in a table based on a matching data in DAX Commands and Tips

Comparing 2 rows in a table based on a matching data

andy919 — Wed, 18 May 2022 06:12:28 GMT

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

Re: Comparing 2 rows in a table based on a matching data

Anonymous — Mon, 23 May 2022 05:40:12 GMT

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.

Re: Comparing 2 rows in a table based on a matching data

andy919 — Tue, 24 May 2022 03:49:12 GMT

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