Forum Discussion
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:
| 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.
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.
4 Replies
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())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.
