cancel
Showing results for
Did you mean:
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?

 Identifier Name 1 Joe 2 John 3 Lucy 1 Joe 2 John 3 Lucy 2 Joe 3 Lucy
1 ACCEPTED SOLUTION

Accepted Solutions
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"
)

6 REPLIES 6
Super User

## Re: Find irregular pairs

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

## Re: Find irregular pairs

@kevlin79

File attached as well

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

Proud to be a Datanaut!

Frequent Visitor

## Re: Find irregular pairs

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

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

Frequent Visitor