Forum Discussion
Creating a measure that can filter multiple values from multiple columns
You're welcome! I might not be able to give the perfect answer without knowing the full structure of your table and its granularity, but if you only have two ingredients, here's a suggestion:
Import your data twice — in the first query, remove Ingredient 2 and Qty 2; in the second, remove Ingredient 1 and Qty 1. (Note: if you have more than two ingredients, this approach won't scale well.)
Then, rename Ingredient 1 and Ingredient 2 to a common column name like Ingredient, and do the same for Qty 1 and Qty 2, renaming both to Qty.
Finally, append the two queries. This functions like an unpivot, but will be faster at runtime since it requires less compute once the data is loaded into the model.
Hi Arash, thank you so much for your quick response. I started going down the unpivot columns path and may revert back to it if absolutely necessary. The problem I was running into there was due to perfomance issues. The example I gave above is extremely simplified. I am working with millions of rows of data and when I begin factoring in customer, region, month, etc, the dataset start compounding rapidly.
You're welcome! I might not be able to give the perfect answer without knowing the full structure of your table and its granularity, but if you only have two ingredients, here's a suggestion:
Import your data twice — in the first query, remove Ingredient 2 and Qty 2; in the second, remove Ingredient 1 and Qty 1. (Note: if you have more than two ingredients, this approach won't scale well.)
Then, rename Ingredient 1 and Ingredient 2 to a common column name like Ingredient, and do the same for Qty 1 and Qty 2, renaming both to Qty.
Finally, append the two queries. This functions like an unpivot, but will be faster at runtime since it requires less compute once the data is loaded into the model.
Thanks again! I hadn't thought of that approach and it just might work. I'll try it and test it when I return to work in the morning and will let you know. I really appreciate it.
Hi krisplikj ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Arash_Bhz for the prompt response!!
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. If the responses have addressed your query, kindly accept it as a solution so other members can easily find it.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you!!
