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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Miguelcrz
Helper I
Helper I

Help with combinations - DAX

Hello, I am trying to know the most frequent combinations of purchase categories per customer with DAX. 

 

CategoryClientQuantity SoldBrandDate
MakeupClient A1Adidas01/01/2010
DermocosmeticClient A2Regiment01/01/2010
SkinClient B1Tommy02/02/2010
AccesoriesClient B3Nike02/02/2010
TabletsClient C1AOC03/02/2010
MakeupClient C1Adidas03/02/2010
DermocosmeticClient C2Regiment03/02/2010
ShirtsClient C1Regiment03/02/2010

 

Combinations: 

1. Makeup + Dermocosmetic = 2 (From Client C and Client A)

2. Skin + Accesories = 1

3. Tablets + Shirts = 1

4. Tablets + Makeup = 1

5. Tablets + Dermocosmetic = 1

6. Tablets + Dermocosmetic + Makeup + Shirts = 1

 

I know it can have a lot of combinations but i also want to know if theres a way to just see the most frequent combinations of 2 categories.

 

Hope u can help me.

 

Thanks! 😄

 

1 ACCEPTED SOLUTION

OK @Miguelcrz I will take a look at the files. Meanwhile, I fixed some bugs in my logic so now I only have unique combinations, no permutations where things are ordered differently!! Updated PBIX is attached, here is the code:

 

Unique Product Combinations = 
    DISTINCT(
        SELECTCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    ADDCOLUMNS(
                        GENERATE(
                            GENERATE(
                                GENERATE(
                                    SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category1",[Category]),
                                    SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category2",[Category])
                                ),
                                SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category3",[Category])
                            ),
                            SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category4",[Category])
                        ),
                        "__Max",MAXX({[Category1],[Category2],[Category3],[Category4]},[Value]),
                        "__Min",MINX({[Category1],[Category2],[Category3],[Category4]},[Value])
                    ),
                    "__Mid1",MINX(FILTER({[Category1],[Category2],[Category3],[Category4]},[Value]<>[__Max]&&[Value]<>[__Min]),[Value]),
                    "__Mid2",MAXX(FILTER({[Category1],[Category2],[Category3],[Category4]},[Value]<>[__Max]&&[Value]<>[__Min]),[Value]),
                    "__Unique",COUNTROWS(DISTINCT({ [Category1], [Category2], [Category3], [Category4]}))
                ),
                "__Index",
                    SWITCH(
                        [__Unique],
                        4,CONCATENATEX({[__Min],[__Mid1],[__Mid2],[__Max]},[Value],","),
                        3,
                            VAR __Max = MAXX({[__Min],[__Mid1],[__Mid2],[__Max]},[Value])
                            VAR __Min = MINX({[__Min],[__Mid1],[__Mid2],[__Max]},[Value])
                            VAR __Mid = MAXX(FILTER({[__Min],[__Mid1],[__Mid2],[__Max]},[Value]<>__Max && [Value]<>__Min),[Value])
                            RETURN
                            CONCATENATEX({__Min,__Mid,__Max},[Value],","),
                        2,CONCATENATEX({[__Min],[__Max]},[Value],","),
                        [__Min]
                    )
            ),
            "Key",[__Index]
        )
    )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

OK, I believe here is part of the answer. This is a table.

 

Unique Product Combinations = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            ADDCOLUMNS(
                GENERATE(
                    GENERATE(
                        GENERATE(
                            SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category1",[Category]),
                            SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category2",[Category])
                        ),
                        SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category3",[Category])
                    ),
                    SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category4",[Category])
                ),
                "__Unique",COUNTROWS(DISTINCT({ [Category1], [Category2], [Category3], [Category4]}))
            ),
            "__Index",
                SWITCH(
                    [__Unique],
                    4,CONCATENATEX({[Category1],[Category2],[Category3],[Category4]},[Value],","),
                    3,CONCATENATEX({[Category1],[Category2],[Category3]},[Value],","),
                    2,CONCATENATEX({[Category1],[Category2]},[Value],","),
                    [Category1]
                )
        ),
        "Key",[__Index]
    )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you @Greg_Deckler . I've uploaded the 2 tables i have so you can help me with the dax. 

Thanks you very much!

 

https://1drv.ms/x/s!AlnMWWMDdqtVlDF8M0d-ni5bEyD8?e=j11ZWU

 

In looking at your data, are you interested in distinct combinations of CATEGORY or SKU or both?

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I would like to have both if u can help me please.

 

Thank you!!

 

OK @Miguelcrz I will take a look at the files. Meanwhile, I fixed some bugs in my logic so now I only have unique combinations, no permutations where things are ordered differently!! Updated PBIX is attached, here is the code:

 

Unique Product Combinations = 
    DISTINCT(
        SELECTCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    ADDCOLUMNS(
                        GENERATE(
                            GENERATE(
                                GENERATE(
                                    SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category1",[Category]),
                                    SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category2",[Category])
                                ),
                                SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category3",[Category])
                            ),
                            SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category4",[Category])
                        ),
                        "__Max",MAXX({[Category1],[Category2],[Category3],[Category4]},[Value]),
                        "__Min",MINX({[Category1],[Category2],[Category3],[Category4]},[Value])
                    ),
                    "__Mid1",MINX(FILTER({[Category1],[Category2],[Category3],[Category4]},[Value]<>[__Max]&&[Value]<>[__Min]),[Value]),
                    "__Mid2",MAXX(FILTER({[Category1],[Category2],[Category3],[Category4]},[Value]<>[__Max]&&[Value]<>[__Min]),[Value]),
                    "__Unique",COUNTROWS(DISTINCT({ [Category1], [Category2], [Category3], [Category4]}))
                ),
                "__Index",
                    SWITCH(
                        [__Unique],
                        4,CONCATENATEX({[__Min],[__Mid1],[__Mid2],[__Max]},[Value],","),
                        3,
                            VAR __Max = MAXX({[__Min],[__Mid1],[__Mid2],[__Max]},[Value])
                            VAR __Min = MINX({[__Min],[__Mid1],[__Mid2],[__Max]},[Value])
                            VAR __Mid = MAXX(FILTER({[__Min],[__Mid1],[__Mid2],[__Max]},[Value]<>__Max && [Value]<>__Min),[Value])
                            RETURN
                            CONCATENATEX({__Min,__Mid,__Max},[Value],","),
                        2,CONCATENATEX({[__Min],[__Max]},[Value],","),
                        [__Min]
                    )
            ),
            "Key",[__Index]
        )
    )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

OK, almost. Table should be:

 

Unique Product Combinations = 
    DISTINCT(
        SELECTCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    GENERATE(
                        GENERATE(
                            GENERATE(
                                SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category1",[Category]),
                                SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category2",[Category])
                            ),
                            SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category3",[Category])
                        ),
                        SELECTCOLUMNS(DISTINCT('Table'[Category]),"Category4",[Category])
                    ),
                    "__Unique",COUNTROWS(DISTINCT({ [Category1], [Category2], [Category3], [Category4]}))
                ),
                "__Index",
                    SWITCH(
                        [__Unique],
                        4,CONCATENATEX({[Category1],[Category2],[Category3],[Category4]},[Value],","),
                        3,CONCATENATEX({[Category1],[Category2],[Category3]},[Value],","),
                        2,CONCATENATEX({[Category1],[Category2]},[Value],","),
                        [Category1]
                    )
            ),
            "Key",[__Index]
        )
    )

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

OK, I think this is close. I created this table also:

 

Customer Unique Product Combinations = 
    DISTINCT(
        SELECTCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    GENERATE(
                        SUMMARIZE('Table',[Client],[Date]),
                        GENERATE(
                            GENERATE(
                                GENERATE(
                                    SELECTCOLUMNS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Client]=EARLIER('Table'[Client]) && [Date]=EARLIER('Table'[Date])),"Category",[Category])),"Category1",[Category]),
                                    SELECTCOLUMNS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Client]=EARLIER('Table'[Client]) && [Date]=EARLIER('Table'[Date])),"Category",[Category])),"Category2",[Category])
                                ),
                                 SELECTCOLUMNS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Client]=EARLIER('Table'[Client]) && [Date]=EARLIER('Table'[Date])),"Category",[Category])),"Category3",[Category])
                            ),
                             SELECTCOLUMNS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Client]=EARLIER('Table'[Client]) && [Date]=EARLIER('Table'[Date])),"Category",[Category])),"Category4",[Category])
                        )
                    ),
                    "__Unique",COUNTROWS(DISTINCT({ [Category1], [Category2], [Category3], [Category4]}))
                ),
                "__Index",
                    SWITCH(
                        [__Unique],
                        4,CONCATENATEX({[Category1],[Category2],[Category3],[Category4]},[Value],","),
                        3,CONCATENATEX({[Category1],[Category2],[Category3]},[Value],","),
                        2,CONCATENATEX({[Category1],[Category2]},[Value],","),
                        [Category1]
                    )
            ),
            "Client",[Client],
            "Key",[__Index]
        )
    )

 

Created this column in Unique Product Combinations:

Column = COUNTROWS(RELATEDTABLE('Customer Unique Product Combinations'))

 

I think it is close, probably still needs some work to get rid of duplicates like Makeup,Makeup,Shirt kind of stuff. PBIX is attached.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

Darn, I have a recipe in my new book that comes out next week, DAX Cookbook that specifically deals with Permutations and Combinations but it is in final editing and everything is locked, I can't even view it.

 

Let me see what I can do.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.