CODEWITHSUNDEEP
powerbidax
Peter Nguy Nguyen
Peter Nguy Nguyen

Reputation: 139

Power BI, DAX: Merging multiple rows based on condition and aggregation

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

enter image description here

The model enter image description here

Upvotes: 1

Views: 1104

Answers (2)

Marcus
Marcus

Reputation: 4015

Depending on your requirements you can do one of three things:

  1. Either hide the Loading Date column altogether as suggested by Peter
  2. Apply a summarization of the Loading Date column (e.g. "Earliest" or "Latest" date, again depending on what makes sense for your use case)
  3. Introduce a concatenating measure that gives you all the different dates separated by a defined separator, on one row in the visualization.

Option 1 and 2 should need little explanation, but for option 3 the measure could look like this (EDIT: Updated with more information about your data model):

Loading Dates = 
IF (
    ISINSCOPE ( 'Fact_Shipping'[BatchNo] ) , // Blanks measure in total row
    CONCATENATEX (
        Fact_Shipping ,
        -- Format function may not be needed depending on your data model settings and requirements
        FORMAT (  
            RELATED ( DimDate[Date] ) , "yyyy-MM-dd" 
        ) ,
        UNICHAR(10) // Newline character
    )
)

You can also use this measure that uses the Fact_Shipping[DateSK] column directly, use the performance analyzer in Power BI or benchmarking tool in DAX Studio to see which of these perform better for your use case:

Loading Dates 2 = 
IF (
    ISINSCOPE ( 'Fact_Shipping'[BatchNo] ) , // Blanks measure in total row
    CONCATENATEX (
        VALUES ( Fact_Shipping[DateSK] ) ,
        LEFT([DateSK], 4) & "-" & MID([DateSK], 5, 2) & "-" & RIGHT([DateSK], 2) ,
        UNICHAR(10) // Newline character
    )
)

Which produces the following result:

enter image description here

Note that your NetWeight Shipped column should be summed using a dedicated measure or using the implicit column measure function in Power BI. I recommend adding an explicit measure to do this:

Total NetWeight Shipped = SUM ( 'Table'[NetWeight Shipped] )

If you are not using an aggregation on the weight column, the visualization will show multiple rows per BatchNo. In the picture above, an implicit column measure is used to summarize the NetWeight Shipped column.

Upvotes: 1

Peter
Peter

Reputation: 12375

Use SUM(NetWeight Shipped) but remove [Loading Date] from the visual, so that it stops filtering the measure, thereby hindering the aggregation.

Upvotes: 0

Related Questions