Help with combinations - DAX

[Miguelcrz]

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

Category	Client	Quantity Sold	Brand	Date
Makeup	Client A	1	Adidas	01/01/2010
Dermocosmetic	Client A	2	Regiment	01/01/2010
Skin	Client B	1	Tommy	02/02/2010
Accesories	Client B	3	Nike	02/02/2010
Tablets	Client C	1	AOC	03/02/2010
Makeup	Client C	1	Adidas	03/02/2010
Dermocosmetic	Client C	2	Regiment	03/02/2010
Shirts	Client C	1	Regiment	03/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! 😄

[Greg_Deckler]

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]
    )

[Miguelcrz]

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

[Greg_Deckler]

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

[Miguelcrz]

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

Thank you!!

[Greg_Deckler · 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]
        )
    )

[Greg_Deckler]

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]
        )
    )

[Greg_Deckler]

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.

[Greg_Deckler]

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.