Forum Discussion
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 |
| 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! 😄
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] ) )
8 Replies
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.
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] )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?
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] ) )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.
