Merging multiple rows based on condition and aggregation

[peternznguyen]

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.

[v-yalanwu-msft · Accepted solution]

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:

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]

Hi, @peternznguyen ;

You could use matrix in power bi not table.

Then set it.

off total.

The final show:

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.

[peternznguyen]

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].

[pbi-novice]

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.

[peternznguyen]

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])," ")

[FreemanZ]

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]))

)

[peternznguyen]

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]

I would like to provide the model

[FreemanZ]

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])

        )

)

[FreemanZ]

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