Forum Discussion
concatenate all values for specific value over multiple rows
dear all,
I like to find combinations in sales where i want to find all categories that someone bought on a day for every row in the sales table.
i have a table like this:
| Customer | Date | Visitkey | Category |
| 1 | 01-01-22 | 1/01-01-22 | a |
| 1 | 01-01-22 | 1/01-01-22 | b |
| 2 | 01-01-22 | 2/01-01-22 | c |
| 2 | 01-01-22 | 2/01-01-22 | a |
| 1 | 02-01-22 | 1/02-01-22 | a |
| 1 | 02-01-22 | 1/02-01-22 | a |
| 2 | 02-01-22 | 2/02-01-22 | b |
| 2 | 02-01-22 | 2/02-01-22 | a |
| 2 | 02-01-22 | 2/02-01-22 | c |
and per visit i wish to determine all the categorynames per visit
the dimension table looks like this:
| Category | categoryname |
| a | a1 |
| b | b1 |
| c | c1 |
| d | d1 |
and the result should look like this:
| Customer | Date | Visitkey | Category | Result |
| 1 | 01-01-22 | 1/01-01-22 | a | a1, b1 |
| 1 | 01-01-22 | 1/01-01-22 | b | a1, b1 |
| 2 | 01-01-22 | 2/01-01-22 | c | a1, c1 |
| 2 | 01-01-22 | 2/01-01-22 | a | a1, c1 |
| 1 | 02-01-22 | 1/02-01-22 | a | a1 |
| 1 | 02-01-22 | 1/02-01-22 | a | a1 |
| 2 | 02-01-22 | 2/02-01-22 | b | a1, b1, c1 |
| 2 | 02-01-22 | 2/02-01-22 | a | a1, b1, c1 |
| 2 | 02-01-22 | 2/02-01-22 | c | a1, b1, c1 |
there is a relationship from category to sales table between category column from 1 - *
HI Anonymous,
You can try to use the following calculated column formula to directly lookup and concatenate dimension table values based on the current category.
Result= VAR list = CALCULATETABLE ( VALUES ( Table[Category] ), FILTER ( Table, [Customer] = EARLIER ( Table[Customer] ) ) ) RETURN CONCATENATEX ( FILTER ( dimension, [Category] IN list ), [categoryname], "," )Regards,
Xiaoxin Sheng
3 Replies
rohit_singhSolution Sage
Hi Anonymous ,
Please try the steps given below :
1) Create a 1:M relationship between the dim and fact tables on the category field.2) Using the relationship defined above, create a new calculated column on the fact table.
Cat name = RELATED(dim_categoryname[categoryname])3) Finally, create another calculated column using the column created above that will give you the desired result.
Daily Categories =CALCULATE(CONCATENATEX(VALUES(fact_categorynames[Cat name]), fact_categorynames[Cat name], " , "),ALLEXCEPT(fact_categorynames,fact_categorynames[Date],fact_categorynames[Customer]))Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊HI Anonymous,
You can try to use the following calculated column formula to directly lookup and concatenate dimension table values based on the current category.
Result= VAR list = CALCULATETABLE ( VALUES ( Table[Category] ), FILTER ( Table, [Customer] = EARLIER ( Table[Customer] ) ) ) RETURN CONCATENATEX ( FILTER ( dimension, [Category] IN list ), [categoryname], "," )Regards,
Xiaoxin Sheng