Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Kudo Kingpin

## Top product combinations sold - DAX measure

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
2 ACCEPTED SOLUTIONS
Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
6 REPLIES 6
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

@michaelsh , I think this has to do with market basket analysis

Kudo Kingpin

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

Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

New Member

Hi Pat, @mahoneypat

Do you hve any tips I could do the same but with combination of 3 products?

Best Regards,
Anita

New Member

Or how can I retrieve the table with eg. top 10 product combos within certain date frame selected on slicer?
Is that possible?

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors