Dax for Summarized table on a condition

[Sam1002]

Hello, I needed help with writing a Dax for summarized table on a condition, meaning with a condition is true group the records and if condition is false don't group the records. 

In below example, Service Folder 8516 have 2 records of item B5068-Y1(in yellow) and here workstatus is same(i.e. 4), so we can summarize the 2 records taking max of createddate, workclosedate, sum of quantities and max of workstatus. But when work status is not same for an item like B5063-K1(in orange), I need to keep both the records in the summarized table. Similarly, for Service folder 1013 item B4130-540-1-Y1(dark green) which is a duplicate and have same work status should be grouped. This grouping needs to be done line level meaning considering item with Service folder and transferID.

Problem Table:

Solution Table:

 

Thanks in Advance

 

[v-angzheng-msft · Accepted solution]

Hi, @Sam1002 

Try to create a calculated table like this:

Table 2 = 
SUMMARIZE(
    'Table',
    [Service Folder],[TRANSFERID],[ITEMID],[RMC Base],[WorkStatus],
    "CREATEDDATETIME",MAX('Table'[CREATEDDATETIME]),"WorkClosedDateTime",MAX('Table'[WorkClosedDateTime]),"QTYTRANSFER",SUM('Table'[QTYTRANSFER]),
    "QTYRECEIVED",SUM('Table'[QTYRECEIVED]),"QTYREMAINSHIP",SUM('Table'[QTYREMAINSHIP])
    )

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sam1002]

Hello @v-angzheng-msft 

This worked really well. Thank you for the efforts 🙂