Forum Discussion
Value contained in another column ?
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
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")
2 Replies
- amitchandak
Super User
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")
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])returnif(isblank(_cnt),"OK","NOK")Thanks a lot !
Have a great day,Greg
