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.
Hi, I am trying to concatenate products that belong to the same order ID to do some market basket analysis. So for example, lets say for an order number that has these products sold in it: Milk, Bread, Milk, and Eggs (the reason why there is two milks is because a customer bought two different brands of milk). I am trying to concatenate these values in a new calculated column in my sales table to be, "Milk-Bread-Eggs" but right now with my current forumla:
Market Basket = CALCULATE( CONCATENATEX(Sales, Sales[Product], “-“ ), FILTER( ALL(Sales), Sales[Transaction ID] = EARLIER(Sales[Transaction ID]) ) )
The result I am getting is, "Milk-Bread-Milk-Eggs". But I don't want the Milk to show twice in my new column, I only want it to appear once.
Does anyone know a way I can edit my current forumla to adjust for this issue?
Thanks! @Cmcmahan
Solved! Go to Solution.
Hi @Wkeith ,
Try to change you formula to something similar to this:
Market Basket = CALCULATE( CONCATENATEX(DISTINCT(Sales[Product]), Sales[Product], “-“ ), FILTER( ALL(Sales), Sales[Transaction ID] = EARLIER(Sales[Transaction ID]) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Wkeith ,
Try to change you formula to something similar to this:
Market Basket = CALCULATE( CONCATENATEX(DISTINCT(Sales[Product]), Sales[Product], “-“ ), FILTER( ALL(Sales), Sales[Transaction ID] = EARLIER(Sales[Transaction ID]) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks! You rock.
Covering 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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |