how to cross check values in several columns?

[Avia]

Hi,

Is there a way to view rows that have repeating valus Iin 8 different columns? how do I cross check them and show them as correlating?

Example (in 4 columns) : 

Name	PH1	PH2	PH3	PH4
A	11111		55555
B	22222
C	33333
D	44444
E	55555
F	66666	22222
G	77777
H	888888
I	999999
J	12121212	11111
K	353535			11111
L	424242
M	424242

An ideal result will be a way to view (in new table/dashboard) :

1. row A and E (they have the same value 55555 )

2. row A and J and K (they have the same value 11111)

3.row B and F (they have the same value 22222)

4. row L and M(they have the same value  424242)

[v-jiascu-msft]

Hi @Avia,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @Avia,

Please refer to the demo in the attachment. You can hide the measure in the Visual Level Filter.

1. Create a table.

PHs =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( Table1[PH1] ),
            VALUES ( Table1[PH2] ),
            VALUES ( Table1[PH3] ),
            VALUES ( Table1[PH4] )
        )
    ),
    ISBLANK ( [PH1] ) = FALSE ()
)

 2. Create a measure.

Measure =
VAR selected =
    VALUES ( PHs[PH] )
RETURN
    IF (
        MIN ( 'Table1'[PH1] ) IN selected
            || MIN ( 'Table1'[PH2] ) IN selected
            || MIN ( 'Table1'[PH3] ) IN selected
            || MIN ( 'Table1'[PH4] ) IN selected,
        1,
        BLANK ()
    )

Best Regards,
Dale

[LivioLanzo]

@Avia

This should be easy if you apply this Power Query transformation:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9JDsAgDAP/kjOX0v3YfX8B4v/fqA3qkqoTJCyHBNk5acRIQnDj5ITSGycthCWxd9kdREq03UNkRNvDe+tjjxAF+fzB3gRREj0yQ1QB7S8QdUD7K6PZWDplaG+MkbPusfiEzZ1hLEvvPP58fwI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, PH1 = _t, PH2 = _t, PH3 = _t, PH4 = _t]),
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"PH1", Int64.Type}, {"PH2", Int64.Type}, {"PH3", Int64.Type}, {"PH4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

and then drop the column 'Value' into the rows section of the matrix and add this measure:

Measure = 
IF(
    HASONEVALUE( Data[Value] ),
    IF(
        COUNTROWS( Data ) > 1,
        CONCATENATEX(
            Data,
            Data[Name],
            ", "
        )
    )
)