Forum Discussion

kevlin79's avatar
kevlin79
Regular Visitor
7 years ago
Solved

Find irregular pairs

I have a data setup where row values in two columns are expected to match but want to identify instances where there is an "irregularity". So in the following table: "1 - Joe" is such a case since th...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    kevlin79

     

    My apologies.I had to go out after my previous post

    Try this Column in this situation.

    File attached as fell

     

    Column 2 =
VAR DistinctCount_Identifiers =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Identifier] ),
        ALLEXCEPT ( Table1, Table1[Name] )
    )
VAR Count_Identifiers =
    CALCULATE ( COUNT ( Table1[Identifier] ), ALLEXCEPT ( Table1, Table1[Name] ) )
RETURN
    IF (
        AND (
            DistinctCount_Identifiers > 1,
            DistinctCount_Identifiers <> Count_Identifiers
        ),
        "I am Irregular"
    )

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

HI kevlin79

 

Try this calculated column

 

Column =
VAR DuplicateCount =
    CALCULATE (
        COUNTROWS ( Table1 ),
        ALLEXCEPT ( Table1, Table1[Identifier], Table1[Name] )
    )
VAR Isthere_a_higher_identifier =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Name] ),
            [Identifier] > EARLIER ( [Identifier] )
        )
    )
RETURN
    IF ( AND ( DuplicateCount, Isthere_a_higher_identifier ), "I am irregular" )