concatenate all values for specific value over multiple rows

[VV24]

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 - *

[v-shex-msft · Accepted solution]

HI @VV24,

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

[VV24]

thanks, its working! and lets say if you have a *-* relationship between those two tables, is there a way to solve it then?

[rohit_singh]

Hi @VV24 ,

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! 😊