cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevlin79 Frequent Visitor
Frequent Visitor

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 there is a 2 - Joe. I would only expect combinations of "1-Joe" or "2-Joe". How would I go about to identify such irregularity?

 

IdentifierName
1Joe
2John
3Lucy
1Joe
2John
3Lucy
2Joe
3Lucy
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Find irregular pairs

@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"
    )

irre.png

 

6 REPLIES 6
Super User
Super User

Re: Find irregular pairs

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" )
Highlighted
Super User
Super User

Re: Find irregular pairs

@kevlin79

 

File attached as well

 

irregular.png

Super User
Super User

Re: Find irregular pairs

Here is a simple measure that you can put in a table with Name:

 

Measure = 
VAR __count = COUNTX(DISTINCT(Table4[Identifier]),[Identifier])
RETURN
IF(__count>1,"Bad","Good")

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


kevlin79 Frequent Visitor
Frequent Visitor

Re: Find irregular pairs

@Zubair_Muhammad 

 

Seems to work! If I also wanted the pair "2 - Joe" to be marked as part of the irregulars, how would the code be adjusted to this?

 

Super thanks

Super User
Super User

Re: Find irregular pairs

@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"
    )

irre.png

 

kevlin79 Frequent Visitor
Frequent Visitor

Re: Find irregular pairs

@Zubair_Muhammad Wonderful, many thanks!!