Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 Vi...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

rohit_singh's avatar
rohit_singh
Solution Sage
4 years ago

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! ðŸ˜Š