Forum Discussion
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 |
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
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" )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
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")
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 Wonderful, many thanks!!
