Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Power BI Community,
I have access to retail market basket data that shows products bought alongside our products. A consumer may buy our products or competitive products along with the original product. I would like to have a master catalog that combines all of these products and descriptions into a new table. We should use the original description when available and use market basket product one when missing. I would like to accomplish this using DAX.
Here is a link to the PBIX file.
I appreciate any help to get the new combined table created.
Solved! Go to Solution.
Hi @Anonymous ,
to simplify the process and you understand better where the values are coming from in the future create a table with the following code:
Unique_Items = UNION ( GROUPBY ( ALL ( Product_Catalog[item]); Product_Catalog[item] ); GROUPBY ( ALL ( market_basket[item_2] ); market_basket[item_2] ); GROUPBY ( ALL ( market_basket[item_3] ); market_basket[item_3] ) )
Then add a calculated column with the following code:
DESCRIPTION = VAR Description_product_catalog = LOOKUPVALUE ( Product_Catalog[description]; Product_Catalog[item]; Unique_Items[item] ) VAR Description_market = LOOKUPVALUE ( Master_Catalog[description]; Master_Catalog[item]; Unique_Items[item] ) RETURN IF ( Description_product_catalog = ""; Description_market; Description_product_catalog )
Should do expected result
Attach PBIX file.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI was able to get a unique list of products using the following DAX for a new table:
Hi @Anonymous ,
Try the following code for your table:
Unique_Items = UNION ( GROUPBY ( ALL ( Product_Catalog[item]; Product_Catalog[description] ); Product_Catalog[item]; Product_Catalog[description] ); GROUPBY ( ALL ( market_basket[description2]; market_basket[item_2] ); market_basket[item_2]; market_basket[description2] ); GROUPBY ( ALL ( market_basket[item_3]; market_basket[description3] ); market_basket[item_3]; market_basket[description3] ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for the response @MFelix The items are repeated if they are present in both 'Market Basket' & 'Product Catalog' I assume because they have different descriptions. Is there a way to use the descriptions from the product catalog table if available and from Market basket if not for a unique list of combined items? If we have to create another 'helper' table as another step that would be ok as well.
Hi @Anonymous ,
to simplify the process and you understand better where the values are coming from in the future create a table with the following code:
Unique_Items = UNION ( GROUPBY ( ALL ( Product_Catalog[item]); Product_Catalog[item] ); GROUPBY ( ALL ( market_basket[item_2] ); market_basket[item_2] ); GROUPBY ( ALL ( market_basket[item_3] ); market_basket[item_3] ) )
Then add a calculated column with the following code:
DESCRIPTION = VAR Description_product_catalog = LOOKUPVALUE ( Product_Catalog[description]; Product_Catalog[item]; Unique_Items[item] ) VAR Description_market = LOOKUPVALUE ( Master_Catalog[description]; Master_Catalog[item]; Unique_Items[item] ) RETURN IF ( Description_product_catalog = ""; Description_market; Description_product_catalog )
Should do expected result
Attach PBIX file.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |