merge rows using DAX

dogburalHK82 — Tue, 16 May 2023 23:53:29 GMT

Hi,

I have purchase order with receipt date and expected date. Now I am trying to evaulate supplier's devliery performace.

At the end I would like to count as below using DAX, rather than sorting them out in power query using Grouping rows.

How can I merge rows using Dax and extract as below?

Each column definition as below

OnTime(any)
- any delivery arrived before promised date regardless of partial or full amount and quality of part.
- In this case, PO111, PDN_3 and PDN_2 as well as PDN_1.
- PO222, PDN_5 and PDN_4

OnTime
- any delivery arrived before promised date regardless of partial or full amount - but should be a good part
- In this case, PO111, PDN_3 and PDN_2 as well as PDN_1
- PO222, none (because it becomes negative receipt quantity, ended up with backorder qty of 1

OnTimeInFull
- any delivery arrived before promised date and in a full amount with correct part
- In this case, PO111, PDN_2 and PDN_1
- PO222, none

Late
- any delivery arrived after promised date
- In this case, PO111, PDN_3 (because it was partial deliver and balance arrived after promised date)
- PO222, PDN_4 and PDN_5

QualityError
- any delivery with incorrect (poor) quality part
- In this case, PO111, none
- PO222, PDN_4 and PDN_5

Re: merge rows using DAX

some_bih — Wed, 17 May 2023 05:39:22 GMT

Try Calculated column in your table with IF statement for your wanted status, like INT(Promised Date - Delivery Date. I hope this help.

dogburalHK82 — Thu, 18 May 2023 01:40:23 GMT

not sure how I can acheive using calculated column

some_bih — Thu, 18 May 2023 05:28:37 GMT

dogburalHK82 check below official Microsoft link. On Step 5 there is example how to use IF function to get some of your status.