Forum Discussion

andy919's avatar
andy919
New Member
4 years ago
Solved

Comparing 2 rows in a table based on a matching data

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

 

AAABBB21231q1q
BBBAAA14561q1q

 

Is anyone able to help? 

Regards,

Andy

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      andy919
      New Member
      4 years ago

      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