Forum Discussion

Miguelcrz's avatar
Miguelcrz
Helper I
6 years ago
Solved

Help with combinations - DAX

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
Greg_Deckler
Community Champion
6 years ago

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.