Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tasos
Helper II
Helper II

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

 

Capture.JPG

 

I hope that my question is quite clear.

 

Thanks,

1 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:

 

sample_table.PNG

 

 

 

 

 

 

 

 

 

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:

 

final_table.PNG

 

 

 

 

 

 

 

 

Hope it helps.

View solution in original post

3 REPLIES 3
danextian
Super User
Super User

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.

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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.

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:

 

sample_table.PNG

 

 

 

 

 

 

 

 

 

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:

 

final_table.PNG

 

 

 

 

 

 

 

 

Hope it helps.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.