cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
peternznguyen
Helper I
Helper I

Merging multiple rows based on condition and aggregation

Hello Communtiy,

 

I having an requirement from business users and need your help.

From the screenshot, we have BatchNo column, for example the BatchNo HL18002040, business users want to merge Loading Date into 1 row if they have same BatchNo and then summerizing NetWeight Shipped togother as the Expecting result. I have tried a lot with DAX language but cannot solve the issue.

Please help.

Thank you in advance.

 

 

peternznguyen_1-1668377368933.png

 

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @peternznguyen ;

Sorry for the late reply, We could merge in powe query.

Add funtion.

= Table.Group(#"Changed Type", {"Batch"}, {{"Netweight", each List.Sum([NetWeight]), type nullable number}, {"a", each Text.Combine(  [Date]," 
"), type nullable date}})

The final show:

vyalanwumsft_0-1669363660135.png

vyalanwumsft_1-1669363945775.png

 


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
v-yalanwu-msft
Community Support
Community Support

Hi, @peternznguyen ;

Sorry for the late reply, We could merge in powe query.

Add funtion.

= Table.Group(#"Changed Type", {"Batch"}, {{"Netweight", each List.Sum([NetWeight]), type nullable number}, {"a", each Text.Combine(  [Date]," 
"), type nullable date}})

The final show:

vyalanwumsft_0-1669363660135.png

vyalanwumsft_1-1669363945775.png

 


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft
Community Support
Community Support

Hi, @peternznguyen ;

You could use matrix in power bi not table.

vyalanwumsft_0-1668489506324.png

Then set it.

vyalanwumsft_1-1668489538900.pngvyalanwumsft_2-1668489560650.png

off total.

vyalanwumsft_3-1668489612471.png

The final show:

vyalanwumsft_4-1668489637030.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Yalan Wu,

Thank you very much for your detailed instructions. I have tried this previously but it did not meet my business users. They want Loading Dates (if more than 1) are being merged into one cell, summerized [Netweight Shippled] and Groupped by BatchNo as my post above. Because their purpose is to export to Excel file for further analysis. Therefore they have asked me to do so on Power BI. The solution of @pbi-novice is closer to my goal but we need to get distinct [Loading Date].

peternznguyen_0-1668491141580.png

 

pbi-novice
Helper I
Helper I

pbinovice_0-1668457179250.png

I found a solution however, this will turn your dates from "Date" format to "String" format. I don't really see a solution otherwise. 

 

Table 2 is a calculated table that groups the original table by "BatchNo"(ID) and sums the "NetWeightShipped"(VALUE)

 

Table 2 = GROUPBY('Table','Table'[ID], "Total", SUMX(CURRENTGROUP(),'Table'[Value]))
 
 
Column is a calculated column that concatenates all date values with a "New Line" charachter per "BatchNo"(ID). Table.[Date] = "Loading Date"
 
Column = CONCATENATEX(FILTER('Table', 'Table'[ID] = 'Table 2'[Table_ID]), 'Table'[Date].[Date], "
")
 
Notes:
 
This assumes that the original table does not have duplicate Loading Date per BatchNo. I believe my DAX expressions will duplicate dates for each occurance per BatchNo.
 
Also, the fact that your dates are in a different table does not matter, as long as it is related to the table properly.

Hi pbi-novice,

I have applied your DAX code, the  "Table 2" works well but "Loading Date" concatenates all the values (screenshot) of a specific BatchNo in the Fact_ShippingKPI table. Working around for using functions such  DISTINCT, ALLNOBLANK, VALUES for Loading Date but not resolved.

 

Table 2 = GROUPBY('Fact_ShippingKPI',Fact_ShippingKPI[BatchNo], "NetWeigh Shipped", SUMX(CURRENTGROUP(),Fact_ShippingKPI[NetWeight]))
 
Loading Date = CONCATENATEX(FILTER('Fact_ShippingKPI', 'Fact_ShippingKPI'[BatchNo] = 'Table 2'[Fact_ShippingKPI_BatchNo]), RELATED(Dim_Date[DateBK])," ")
 

peternznguyen_0-1668470848775.png

 

FreemanZ
Community Champion
Community Champion

Supposing your table named Data, try to create a new table with the code below:
SumTable=
ADDCOLUMNS(
    SUMMARIZE(Data, Data[BatchNo], Data[Loading Date]), 
    "WeightShipped",
     CALCULATE(SUM(Data[NetWeight Shipped]))
)

Hi FreemanZ, 
 
Thank you for your quick response,
Actually BatchNo and Loading Date are on two different tables, I have applied your DAX, but the result is still the same.
 
SumTable =
ADDCOLUMNS(
        SUMMARIZE(Fact_ShippingKPI, Fact_ShippingKPI[BatchNo], CompletionDate[DateBK]),
        "WeightShipped",
        CALCULATE(SUM(Fact_ShippingKPI[NetWeight])
    ))
 
peternznguyen_0-1668379683843.png

 

I would like to provide the model

peternznguyen_0-1668454222803.png

 

Try this:
 
SumTable=
VAR _table1 = SUMMARIZE(Fact_ShippingKPI, Fact_ShippingKPI[BatchNo])
RETURN
ADDCOLUMNS (
    _table1,
    "Loading Date"
    CALCULATE (
        CONCATENATEX(
            _table1,
            DIM_Date[DateSK],
            UNICHAR(10)
        ),
        "WeightShipped",
        CALCULATE(
            SUM(Fact_ShippingKPI[NetWeight])
        )
)

@peternznguyen If that still does not work, could you please simify your data and send me the pbix files. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.