Find matching values in many-to-one relation

[Anonymous]

Hey all,

Currently I am having trouble with matching 2 columns (A and B) in 2 different tables (Table 1 and Table2). These columns have a Many-to-One relationship (Table1[A] to Table2[B] = n:1). I am trying to find out for all values in B if these exist in A (Outcome as provided in Table2[Exists in A?]).

What would this problem be solved in the easiest method?

MWE:

Table1

Index	A
1	This value
2	Another value
3	This value
4	Not this value
5	This is a string
6	Another value

Table2

Index	B	Exists in A?
1	This value	Yes
2	Another value	Yes
3	More values	No
4	This is a string	Yes

[Anonymous · Accepted solution]

Hi @Anonymous ,

If you only want to campare column [A] and [B].

Column1 = IF(Table2[B] in VALUES(Table1[A]),"YES","NO")

If you want to comapre both index and columns.

Column2 = IF(Table2[B] = LOOKUPVALUE(Table1[A],Table1[Index],Table2[Index]),"YES","NO")

Best Regards,

Jay

[CNENFRNL]

What's worth mentioning is,

your data model is a typically negative example to avoid, namely "referential integrity violation". Easy to google this jargon and enough solutions to it.

[Anonymous]

Funny you would mention the "referential integrity violation" because what I am looking for in the real problem is actually a method to spot these violations and mark them such that these are easy to find and can be dealt with.

[SpartaBI]

@Anonymous add this column:

Exist in A? = 
    IF(
        Table2[Value] IN CALCULATETABLE(VALUES(Table1[Value])),
        "Yes",
        "No"
    )