CODEWITHSUNDEEP
excel-formulapowerbipowerquery
Pepe
Pepe

Reputation: 55

Power BI Find duplicates and then find if they are exact duplicates within other coulmns

I am trying first to find the duplicates of a CODE (random serial number). I was able to count the duplicates and see that a couple have a values >1

Rule1 = 

Var CountDups = [CODE#]
RETURN

CALCULATE(
    COUNTROWS('Table'),
    all('Table'),
    'Table'[CODE#] = CountDups 
)

That allowed us to find duplicates and how many times they are duplicated.

enter image description here

I want to compare if two other columns (E and F) within the table match.

Rule 2 = 
IF(
 'Table'[Rule1]>1, 
Check if INFO1=INFO1 (comparing them to each other)

.....

Rule3 = 
IF(
 'Table'[Rule1]>1, 
Check if INFO2=INFO2 (comparing them to each other)

I have attached an example. So, once we have a duplicate CODE#, I want to make sure that column B (INFO1) and column C (INFO2) are consistent.

I have attached the logic I am pursuing in parentheses inside columns E and F.

The other rows for column E and F can be populated, but I only want to focus on duplicates. Once a duplicate is found, show/test that they are exact duplicates across the other columns.

Upvotes: 1

Views: 1633

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

You need these below 3 measures-

Rule1 = 

VAR current_row_code = compare_row_column[code]

RETURN
CALCULATE(
    COUNTROWS(compare_row_column),
    FILTER(
        ALL(compare_row_column),
        compare_row_column[code] = current_row_code
    )
)

rule 2 = 

VAR current_row_code = compare_row_column[code]

VAR info_1_count =
CALCULATE(
    COUNT(compare_row_column[info1]),
    FILTER(
        ALL(compare_row_column),
        compare_row_column[code] = current_row_code
    )
)

VAR info_1_distinct_count =
CALCULATE(
    DISTINCTCOUNT(compare_row_column[info1]),
    FILTER(
        ALL(compare_row_column),
        compare_row_column[code] = current_row_code
    )
)

RETURN 
IF(
    info_1_count = 1, 
    BLANK(),
    IF(
        info_1_count = info_1_distinct_count, 
        "No", 
        "Yes"
    )
)

rule 3 = 

VAR current_row_code = compare_row_column[code]

VAR info_2_count =
CALCULATE(
    COUNT(compare_row_column[info2]),
    FILTER(
        ALL(compare_row_column),
        compare_row_column[code] = current_row_code
    )
)

VAR info_2_distinct_count =
CALCULATE(
    DISTINCTCOUNT(compare_row_column[info2]),
    FILTER(
        ALL(compare_row_column),
        compare_row_column[code] = current_row_code
    )
)

RETURN 
IF(
    info_2_count = 1, 
    BLANK(),
    IF(
        info_2_count = info_2_distinct_count, 
        "No", 
        "Yes"
    )
)

Here is the final output-

enter image description here

Upvotes: 1

Related Questions