Forum Discussion

Tasos's avatar
Tasos
Helper II
8 years ago
Solved

Summarising table based on multiple values

 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,

Need Help
Show and Tell
Tips and Tricks
  • MarcoRotta's avatar
    MarcoRotta
    8 years ago

    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.

3 Replies

  • danextian's avatar
    danextian
    Super User
    8 years ago

    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.

     

    • MarcoRotta's avatar
      MarcoRotta
      Resolver I
      8 years ago

      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.

    • Tasos's avatar
      Tasos
      Helper II
      8 years ago

      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.