Forum Discussion

Sam1002's avatar
Sam1002
Regular Visitor
4 years ago
Solved

Dax for Summarized table on a condition

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

 

ALLEXCEPT
groupby
summarize table
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

2 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.