Flag Matching Rows in Different Tables

[newpbiuser01]

Hello,

I am trying to flag rows with matching values in two different tables. As an example, I have two tables, Table1 with a list of items, and some reference data tied to the items. Table2 is a smaller table with only 1 column and just has a list of items. The goal is to go through Table1 and flag any items found in Table2. I can't do this in a calculated column because I'll be using the userprincipalname() function to filter items by Shopper in my table.  Is it possible to do it in a measure? Since these tables don't have the same number of columns, I can't use the intersect function so I'm at a loss as to how to implement it. 

Table1: 

Category	Item	Price	Quantity	Shopper
Fruit	Apple	2	1	A
Fruit	Orange	3	1	B
Fruit	Pear	5	1	C
Vegetable	Spinach	10	1	A
Vegetable	Tomato	6	2	V
Office Supplies	Pen	5	1	C
Office Supplies	Markers	2	1	E
Household Supplies	Pape Towel	20	1	D
Household Supplies	Detergent	3	1	G

Table2: 

Item

Apple

Orange

Pear

Spinach

Desired Result: 

Category	Type	Price	Quantity	Result
Fruit	Apple	2	1	Yes
Fruit	Orange	3	1	Yes
Fruit	Pear	5	1
Vegetable	Spinach	10	1	Yes
Vegetable	Tomato	6	2
Office Supplies	Pen	5	1
Office Supplies	Markers	2	1
Household Supplies	Pape Towel	20	1
Household Supplies	Detergent	3	1

Would anyone know how I could implement this? I'd really appreciate any help. 

Thank you. 

[amitchandak]

@newpbiuser01 , You can add a new column in Table1

New col =

Var _cnt = countx(filter(Table2, Table1[Item] = Table2[Item]), Table2[Item])

return

if(isblank(_cnt), blank(), "Yes")

[newpbiuser01]

Hi @amitchandak,

Would you know if/how I could implement this as a measure? The reason I ask is, the goal is to make this "filterable" by the Shopper - which in my dataset is derived from the user() function. Unfortunately, I can't use that function in a calculated column. 

Thank you!

[v-tangjie-msft · Accepted solution]

Hi @newpbiuser01 ,

You can create a measure.

Result = IF(MAX('Table1'[Item]) in VALUES('Table2'[Item]), "Yes", BLANK())

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[newpbiuser01]

Thank you @v-tangjie-msft!