Calculated column to see if specific value exists in related table?

[jd8766]

Hi, I have the below 2 tables and want to add a calc column to the first one to indicate whether or not a value for that CompanyID exists in the 2nd table with a product of 6. So for CompanyID 1, the column will say true as the company ID exists in the 2nd table and also has a product of 6.

What would be the best way of achieving this?

[v-yangliu-msft · Accepted solution]

Hi  @jd8766 ,

Here are the steps you can follow：

1. Create calculated column.

New Column =
var _selectID=SELECTCOLUMNS('Table2',"ID",'Table2'[CompanyID])
return
IF(
    'Table1'[CompanyID] in _selectID && MAXX(FILTER(ALL(Table2),'Table2'[CompanyID] in _selectID),[Product]) = 6 ,"True","Flase"

)

2. Result:

Best Regards,

Liu Yang

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