indicator for duplicate values from 2 tables

[Sander1401]

Hi all,

I have an example with 2 tables connected via "append queries". Both have 10 customernumbers. 3 customernumbers in table2 are also in table1 (so 17 unique customernumbers).
I want to make a graph1 with the results from table1, and a graph2 with the unique results of table2 (so excluding the duplicate values). 

Can anyone help me with an indicator for table2 which indicates that customernumber 8, 9 and 10 are duplicates?

I would like to use 1 table:

a copy to my test-pbix:

https://www.dropbox.com/scl/fi/jxrlhh39bay39eqa4enq9/test.pbix?rlkey=54ce046hrxscie7lpkw56c0ha&st=dl... 

[DataNinja777 · Accepted solution]

Hi @Sander1401 ,

Yes, you can create an indicator for this. The best approach is to add a calculated column to your appended table using a DAX formula. This new column will check each row and place a "Duplicate" flag on any row from tabel2 where the customernr also exists in tabel1.

To create this column, go to the Data View in Power BI, select your appended table, and click New column. Then, enter the following DAX formula. This formula first checks if a row is from tabel2 and if its customernr is present in the set of customer numbers belonging to tabel1. It will then label the row accordingly.

Duplicate Indicator = 
VAR isFromTable2 = 'Appended Table'[Tabel] = "tabel2"
VAR currentCustomer = 'Appended Table'[customernr]
VAR isInTable1 =
    CONTAINS(
        FILTER('Appended Table', 'Appended Table'[Tabel] = "tabel1"),
        'Appended Table'[customernr], currentCustomer
    )

RETURN
    IF(isFromTable2 && isInTable1, "Duplicate", "Unique")

Once you've created the Duplicate Indicator column, you can easily build your graphs. For your first graph showing results from tabel1, simply filter the visual where the Tabel column is "tabel1". For your second graph showing the unique results from tabel2, apply two filters to the visual: filter the Tabel column to "tabel2" and filter your new Duplicate Indicator column to "Unique". This will exclude the customer numbers shared with tabel1.

Best regards,

[jgeddes]

You can create a calculated column in Table 2 that checks if the current 'customernr' is in the values of Table 1 customernr.

Column = 
[customernr] IN VALUES(Table1[customernr])

This will result in something like...