Summarising table based on multiple values

[Tasos]

 Hello,

I am looking for something that can be either quite easy or extremely difficult.

I have a table like the following one where different lines may have the same ID and same or different values in a second column. I need to create a new table (by summarising the original one) like this:

• 100000019 "Home & Complementary"
• 100000020 "Home" 
• 100000021 "Underwear"
• 100000028 "Womenswear"
• etc

I hope that my question is quite clear.

Thanks,

[danextian]

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.

[Tasos]

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.

[MarcoRotta · Accepted solution]

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.