cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
A7T57ZZ
Frequent Visitor

Combine Rows for Single Order in DAX

I am trying to combine rows with the same in DAX for a visual.  Right now the DB is showing this as two or multiple different lines based on quantity status.  The top chart shows how it looks currently.  The bottom chart is how I would like it to look in my visual.  There are many other columns that would be displayed, but this is the only part I am struggling to make happen.  I am open to a M Query solution as well, but thought there should be some way to do this in DAX.

 

SO - Doc & LineLine Item QtyOBD Line Item Qty
5507815077100900
55078150771001200

550781507710

4800 

550781507710

0600

 

 

SO - Doc & LineLine Item QtyOBD Line Item QtyBackorder
550781507710480027002100
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@A7T57ZZ Seems like a simple Group By:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1MLcwBBLmhgZKOkogbGlgoBSrg1XK0Ai7nImFAYjCpc0MpCsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SO - Doc & Line" = _t, #"Line Item Qty" = _t, #"OBD Line Item Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SO - Doc & Line", Int64.Type}, {"Line Item Qty", Int64.Type}, {"OBD Line Item Qty", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SO - Doc & Line"}, {{"Line Item QTY", each List.Sum([Line Item Qty]), type nullable number}, {"OBD Line Item Qty", each List.Sum([OBD Line Item Qty]), type nullable number}})
in
    #"Grouped Rows"

Or in DAX:

Table = SUMMARIZE('Table'[SO - Doc & Line],"Line Item Qty",SUM([Line Item Qty]),"OBD Line Item Qty",SUM([OBD Line Item Qty]))

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @A7T57ZZ 

why don't you just summarize by sum?

This is just one of 30k different orders that I need to display.  I want the Sum for the two columns by based on the first column.  Essentially it will take 50k lines down to the 30k indivudual orders and allow me to calculate the total qty of shipped and the remaining qty to ship.  Apologies, I should have been more clear.

 

@A7T57ZZ 

That doesn't change anything. Still don't understand why you cannot summarize the columns by sum? Or just create a simple SUM measures?

You are correct, I used a simple summarize.  I was overcomplicating things trying to account for other columns that contain uniuque values.  Going to use MAX/MIN or COUNT to take care of that issue for my purpose.  Your response is greatly appreciated!

Greg_Deckler
Super User
Super User

@A7T57ZZ Seems like a simple Group By:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1MLcwBBLmhgZKOkogbGlgoBSrg1XK0Ai7nImFAYjCpc0MpCsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SO - Doc & Line" = _t, #"Line Item Qty" = _t, #"OBD Line Item Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SO - Doc & Line", Int64.Type}, {"Line Item Qty", Int64.Type}, {"OBD Line Item Qty", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SO - Doc & Line"}, {{"Line Item QTY", each List.Sum([Line Item Qty]), type nullable number}, {"OBD Line Item Qty", each List.Sum([OBD Line Item Qty]), type nullable number}})
in
    #"Grouped Rows"

Or in DAX:

Table = SUMMARIZE('Table'[SO - Doc & Line],"Line Item Qty",SUM([Line Item Qty]),"OBD Line Item Qty",SUM([OBD Line Item Qty]))

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you!  I used the DAX Summarize that I have used many times, but think I was trying to over complicate what I was trying to accomplish.  I have many other columns with unique values, but will have to use a COUNT or MIN/MAX to display how  I want them.  

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.