Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I need to create a Measure that shows which Two-Product Combinations are most common.
For example, here A,B is most common, because 3 customers have it.
Although D is the most common product with 4 customers, but I don't want to see it in my measure, because it is sold alone, not as a combination.
So I need my measure to return "A,B"
Thanks!
Customer | Product |
111 | A |
111 | B |
111 | C |
222 | X |
222 | Y |
222 | Z |
333 | D |
444 | D |
555 | D |
666 | D |
777 | A |
777 | B |
888 | A |
888 | B |
999 | X |
999 | Y |
Solved! Go to Solution.
Basket analysis is probably the best way (didn't confirm), but here is another approach. Put this measure in a card to get "A,B" as the result.
Highest Combo =
VAR cj1 =
DISTINCT ( SELECTCOLUMNS ( Products, "Product1", Products[Product] ) )
VAR cj2 =
DISTINCT ( SELECTCOLUMNS ( Products, "Product2", Products[Product] ) )
VAR cj =
FILTER ( CROSSJOIN ( cj1, cj2 ), [Product1] <> [Product2] )
VAR summary =
ADDCOLUMNS (
cj,
"@total",
VAR prod1 = [Product1]
VAR prod2 = [Product2]
RETURN
COUNTROWS (
FILTER (
ALL ( Products[Customer] ),
AND (
NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod1 ) ) ),
NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod2 ) ) )
)
)
)
)
VAR productslist =
CONCATENATEX (
TOPN ( 1, summary, [@total], DESC, [Product1], ASC ),
[Product1] & ", " & [Product2]
)
RETURN
productslist
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi,
You may download my PBI file from here. With some more effort, i may be able to get A&B but you may take it forward from here.
Hope this helps.
Hi,
You may download my PBI file from here. With some more effort, i may be able to get A&B but you may take it forward from here.
Hope this helps.
@michaelsh , I think this has to do with market basket analysis
https://finance-bi.com/power-bi-basket-analysis/
https://businessintelligist.com/2017/03/27/market-basket-analysis-using-power-bi/
https://www.daxpatterns.com/usecases/market-basket-analysis/
https://www.mssqltips.com/sqlservertip/5428/market-basket-analysis-in-r-and-power-bi/
Yes, this is a Basket Analysis question.
I have researched all these links before I posted. All these topics are similar but they are not answering my case.
I am looking for a specific solution for my case, please.
Thank you
Basket analysis is probably the best way (didn't confirm), but here is another approach. Put this measure in a card to get "A,B" as the result.
Highest Combo =
VAR cj1 =
DISTINCT ( SELECTCOLUMNS ( Products, "Product1", Products[Product] ) )
VAR cj2 =
DISTINCT ( SELECTCOLUMNS ( Products, "Product2", Products[Product] ) )
VAR cj =
FILTER ( CROSSJOIN ( cj1, cj2 ), [Product1] <> [Product2] )
VAR summary =
ADDCOLUMNS (
cj,
"@total",
VAR prod1 = [Product1]
VAR prod2 = [Product2]
RETURN
COUNTROWS (
FILTER (
ALL ( Products[Customer] ),
AND (
NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod1 ) ) ),
NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod2 ) ) )
)
)
)
)
VAR productslist =
CONCATENATEX (
TOPN ( 1, summary, [@total], DESC, [Product1], ASC ),
[Product1] & ", " & [Product2]
)
RETURN
productslist
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat, @mahoneypat
Do you hve any tips I could do the same but with combination of 3 products?
Best Regards,
Anita
Or how can I retrieve the table with eg. top 10 product combos within certain date frame selected on slicer?
Is that possible?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |