Forum Discussion
Advocate IReturn Missing or Extra Values of a Text String from a Non-Related Table
Hi All,
Hope you can help me in meeting several conditons for 2 calculated columns.
I have 2 tables Table1 and Table2 which are unrelated.
I need 2 calculated columns to return the missing or extra contents that were dispensed on Table1 from the Mandatory contents per bundle listed on Table2.
Also DAX should also be able to handle OR (Bundle5) & AND conditions (Bundle6).
Any approach or if possible to incorporate all conditions into 1 calculated column is greatly appreciated.
| Table 1 | ||||
| Invoice No | Package Type | Dispensed Contents | Desired Output: Missing | Desired Output: Extra |
| 123451 | Bundle1 | Rice Pasta Milk Butter Sugar | None | Pasta |
| 123452 | Bundle2 | Flour Sugar | None | None |
| 123453 | Bundle1 | Flour Butter | Rice Milk Sugar | Flour |
| 123454 | Bundle5 | Pasta Flour | None | None |
| 123455 | Bundle6 | Sugar Pasta Flour | Sugar | None |
| Table 2 | |
| Package Type | Mandatory_Contents |
| Bundle1 | Rice Milk Sugar Butter |
| Bundle2 | Sugar Flour |
| Bundle3 | Spices Sugar Butter |
| Bundle4 | Fish Shrimp Meat |
| Bundle5 | Rice or Pasta Flour |
| Bundle6 | Sugar Sugar Pasta Flour |
Many Thanks!!!
Hi o-johnralphp ,
Thanks for reaching out to the Microsoft fabric community forum.
I tried implementing your requirement using LOOKUPVALUE, but it doesn’t work with your data because LOOKUPVALUE can only handle exact text matches, not concatenated text strings like the ones in your column. It would only work if you split the text into separate rows or values, which would likely disrupt your data structure.Instead you can consider using PySpark in Fabric Notebook’s for this type of transformation, as it provides much greater flexibility when working with string operations. Instead of relying on complex DAX calculated columns, you can easily join Table1 and Table2 on Package Type using join(), bringing in the mandatory contents for each package. Once the tables are joined, you can use PySpark’s string and array functions such as split(), array_except(), and array_intersect() to compare the dispensed and mandatory items efficiently. This allows you to identify which items are missing or extra for each record.
I hope this information helps. Please do let us know if you have any further queries.
Thank you
7 Replies
Hi o-johnralphp ,
Thanks for reaching out to the Microsoft fabric community forum.
I tried implementing your requirement using LOOKUPVALUE, but it doesn’t work with your data because LOOKUPVALUE can only handle exact text matches, not concatenated text strings like the ones in your column. It would only work if you split the text into separate rows or values, which would likely disrupt your data structure.Instead you can consider using PySpark in Fabric Notebook’s for this type of transformation, as it provides much greater flexibility when working with string operations. Instead of relying on complex DAX calculated columns, you can easily join Table1 and Table2 on Package Type using join(), bringing in the mandatory contents for each package. Once the tables are joined, you can use PySpark’s string and array functions such as split(), array_except(), and array_intersect() to compare the dispensed and mandatory items efficiently. This allows you to identify which items are missing or extra for each record.
I hope this information helps. Please do let us know if you have any further queries.
Thank you
o-johnralphpHi Anonymous ,
Many thanks for the effort.
- o-johnralphp
Im getting an error "Failed to resolve name "STRING_SPLIT. It is not a valid table,variable, or function name.
Thanks
- Natalie_iTalent
Administrator
Hello o-johnralphp,
The comment you responded to has been removed from this post for being inaccurate.
Best,
Natalie H.
Community Manager
Hi o-johnralphp
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.- o-johnralphp
Hi Anonymous ,
Thank you for your time in trying to come up with solution to my requirement and for pointing why this not possible with DAX. Im still new with Power BI so PySpark will be quite a challenge and Im not sure if it will be applicable in my work settings. Thanks again
Hi o-johnralphp ,
We really appreciate your efforts and for letting us know the update on the issue.
Please continue using fabric community forum for your further assistance.
Thank you
