Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! 😄
Solved! Go to 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]
)
)
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?
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]
)
)
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |