Check if combination in table occurs in other table

[Nijlal01]

Hi,

I would like to create a column or measure in table 2 which checks if a combination occurs in table 1. So for example product=a and customer=2 does not occur in table 1 so the result in table 2 for this combination is 'Not OK'.

The tables are related on product.

Thanks already!

Table1 

Product	Customer
a	1
b	2
c	3

Table2

Product	Customer	result
a	1	OK
a	2	NOT OK
b	2	OK
c	3	OK
c	2	NOT OK

[CNENFRNL]

@Nijlal01 , not knowing the cardinality of Table1, I use two unrelated tables and the following measure,

Result = 
IF (
    CALCULATE (
        ISEMPTY ( Table1 ),
        TREATAS ( Table2, Table1[Product], Table1[Customer] )
    ),
    "NOT Ok",
    "OK"
)

[Nijlal01]

Thanks! It almost works. In the real example however both tables do have more columns. It seems that therefore this option does not work completely. However only the 2 columns (like in the example) have to be compared. Any suggestion how to solve this? 

[CNENFRNL · Accepted solution]

@Nijlal01 , you may tweak the measure this way

Result = 
IF (
    CALCULATE (
        ISEMPTY ( 'Product Filtered GlobalID' ),
        TREATAS (
            SUMMARIZE ( ItemsMapping, ItemsMapping[Code], ItemsMapping[ID] ),
            'Product Filtered GlobalID'[Product.ProductCode],
            'Product Filtered GlobalID'[Product.ProductGlobalID]
        )
    ),
    "NOT Ok",
    "OK"
)

[harshnathani]

Hi @Nijlal01 ,

Create a Calculated Column in Table 2.

Final Column = IF (Table2[Customer] = LOOKUPVALUE(Table1[Customer],Table1[Product],Table2[Product]) , "OK" , "Not Ok")

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)