Value contained in another column ?

[Greg_MSFL]

Hi everyone,
I search on internet but until now couldn't find a way to answer my problem :

I have two tables (and many others, but for this problem, only these two are necessary 🙂 ) :

The first one : contains all the manufacturers (column FABRICANT) that are forbidden for destinations (column ID_CONT).

Example :

The second one : contains all the products (column ID_ART), which are possible to send in the destinations (ID_CONT), and contains all the manufacturer of the products (columns FABRICANT).

Example (I made it in Excel, but it's in PowerBI) :

What I want : - for each product (ID_ART), to know if it's possible to send it in to the destinations. I want it in a new column in my second table.

Example :

Explanation : AA2_DISPATCH1, product 2, contains "SHTROUMPF" in his FABRICANT, so it's KO, because it is forbidden.

I could easily do it if I had only one fabricant for each product, but as you see it's not the case (up to 6 fabricant for one product...).

I'm sure there must be a quite easy way for this, but I can't find it ...

OH, I want the solution in DAX if possible.

Thank you for reading me,

Greg

[amitchandak · Accepted solution]

@Greg_MSFL , based on what I got

New column in Table 2  =

var _cnt = countx(filter(Table1, table1[ID_CONT] = table2[ID_CONT] && table1[FABRICANT] = table2[FABRICANT] ) , table1[ID_CONT] )

return

if(isblank(_cnt), "OK", "KO")

[Greg_MSFL]

Hi amitchandak,

Awesome, it works perfectly !

I just changed it a little, because I want to know if the value of Table1 is contained in Table2. So it's now :

Column2 =

var _cnt = countx(filter(Table1,Table1[ID_CONT]=Table2[ID_CONT] && CONTAINSSTRING(Table2[FABRICANT],Table1[FABRICANT])),Table1[ID_CONT])

return

if(isblank(_cnt),"OK","NOK")

Thanks a lot !
Have a great day,

Greg