Forum Discussion
Summarising table based on multiple values
Hi there.
In the example below I'm using your original data table layout (as in your picture) but named as "Data" in my model:
To create a summarized table using DAX, just go to "Modeling > New Table" and type:
New Table = values(Data[orders])
This creates a new table with the unique values of orders. You can change "New Table" above to whatever name you like for the table, of course.
Now you can go to "Modeling > New Column" and add a DAX expression to concatenate the different values in different rows for each order, which is:
ConcatenatedValues = CONCATENATEX(FILTER(Data,Data[orders]='New Table'[orders]),Data[department]," | ")
In the expression above, the last parameter is the separator you want. I've used "|", but it could be you "&" or anything else.
Final result:
Hope it helps.
Hi Tasos
Correct me If I am wrong but it appears to me that you simply want all Home and Complementary products columns to be treated as one. If so, you may try creating a calculated column in DAX using this formula
Department2 =
IF (
'TableName'[Department] = "Home"
|| 'TableName'[Department] = "Complementary products",
"Home and Complementary",
'TableName'[Department]
)
Then use this newly calculated column in the table visual instead of the old one.
Hi there.
In the example below I'm using your original data table layout (as in your picture) but named as "Data" in my model:
To create a summarized table using DAX, just go to "Modeling > New Table" and type:
New Table = values(Data[orders])
This creates a new table with the unique values of orders. You can change "New Table" above to whatever name you like for the table, of course.
Now you can go to "Modeling > New Column" and add a DAX expression to concatenate the different values in different rows for each order, which is:
ConcatenatedValues = CONCATENATEX(FILTER(Data,Data[orders]='New Table'[orders]),Data[department]," | ")
In the expression above, the last parameter is the separator you want. I've used "|", but it could be you "&" or anything else.
Final result:
Hope it helps.
Hello both,
Thank you for the replies and apologies for my late reply.
danextian, what you proposed could work, however, I have had multiple combinations and therefore your approach wasn't easy to be applied. What MarcoRotta had proposed worked for me.
Once again, thank you for your time and the support.
