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

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

     

    • Miguelcrz's avatar
      Miguelcrz
      Helper I
      6 years ago

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

       

      Thank you!!

       

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    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]
        )
    )