Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
newpbiuser01
Helper IV
Helper IV

Flag Matching Rows in Different Tables

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: 

CategoryItemPriceQuantityShopper
FruitApple21A
FruitOrange31B
FruitPear51C
VegetableSpinach101A
VegetableTomato62V
Office SuppliesPen51C
Office SuppliesMarkers21E
Household SuppliesPape Towel201D
Household SuppliesDetergent31G

 

Table2: 

Item
Apple
Orange
Pear
Spinach

 

Desired Result: 

CategoryTypePriceQuantityResult
FruitApple21Yes
FruitOrange31Yes
FruitPear51 
VegetableSpinach101Yes
VegetableTomato62 
Office SuppliesPen51 
Office SuppliesMarkers21 
Household SuppliesPape Towel201 
Household SuppliesDetergent31 

 

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

 

Thank you. 

1 ACCEPTED SOLUTION

Hi @newpbiuser01 ,

 

You can create a measure.

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

vtangjiemsft_0-1693274103887.png

 

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. 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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")

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!

Hi @newpbiuser01 ,

 

You can create a measure.

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

vtangjiemsft_0-1693274103887.png

 

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. 

Thank you @v-tangjie-msft!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.