DAX - Measurement support

[Ericwhv]

Hi All

Thank you everyone!

Last time, I asked to build a measurment about how to check the "Last Leg"

Pervoius Question

Now, i am not sure if anyone could help me to build a measurement to define the shipment delivered or not?

Backgroud:

if GNR origin = left(Consol Lane,3), that is "First Leg"

if GNR Dest = Right(Consol Lane,3), that is the "Last Leg"

the rest should define as "Transit Leg"

Assume every shipment should has a "Last Leg"

HB is the number to define / group the shipment together.

Requirment:

if the shipment missing last leg, the result will need to show "Not Arrive, missing last leg"

if the shipment has a last leg, and there are no arrival date on the last leg, the result will need to show "Not Arrive"

if the shipment has a last leg, and arrive date is there, the result will be "Arrived"

HB	IC	Consol Lane	Movement Lane	GNR Orign	GNR Dest	Arrival date	Leg Check	Result
H4805777	AMS1234	AMS-FRA	AMS-BUH	AMS	BUH	01-Aug	First Leg	Not arrive
H4805777	BUD4567	BUD-BUH	AMS-BUH	AMS	BUH		Last Leg	Not arrive
H4805777	FRA5678	FRA-BUD	AMS-BUH	AMS	BUH	03-Aug	Transit Leg	Not arrive
H4805792	AMS9999	AMS-FRA	AMS-BUH	AMS	BUH	02-Aug	First Leg	Not Arrive, Missing last leg
H4805792	FRA8888	FRA-BUD	AMS-BUH	AMS	BUH		Transit Leg	Not Arrive, Missing last leg
H55778	AMS5555	AMS-FRA	AMS-BUH	AMS	BUH	03-Aug	First Leg	Arrived
H55778	FRA6666	FRA-BUD	AMS-BUH	AMS	BUH	04-Aug	Transit Leg	Arrived
H55778	BUD7777	BUD-BUH	AMS-BUH	AMS	BUH	05-Aug	Last Leg	Arrived

Thank you every one!!!

[Fowmy · Accepted solution]

@Ericwhv 
I added two columns, one calculate the Leg Check and the 2nd for the  result

Leg Check = 
SWITCH(
    TRUE(),
    LEFT(Table6[Consol Lane],3) = Table6[GNR Orign] , "First Leg",
    RIGHT(Table6[Consol Lane],3) = Table6[GNR Dest] , "Last Leg",
    "Transit Leg"
)

Check = 
VAR __LegCheck = Table6[Leg Check]
VAR __HB = Table6[HB]
VAR __T1 = FILTER( Table6, Table6[Leg Check] = "Last Leg" && Table6[HB] = __HB)
VAR __T2 = FILTER( Table6, Table6[Leg Check] = "Last Leg" && Table6[Arrival date]=BLANK() && Table6[HB] = __HB)
VAR __T3 = FILTER( Table6, Table6[Leg Check] = "Last Leg" && Table6[Arrival date]<>BLANK() && Table6[HB] = __HB)
RETURN
    SWITCH(
        TRUE(),
        ISEMPTY(__T1), "Not Arrived, Missing last leg",
        NOT ISEMPTY(__T2), "Not Arrived",
        NOT ISEMPTY(__T3), "Arrived"        
)

Result

[Ericwhv]

Hi @Fowmy 

not sure if you could help me?
the above quesiton, what if i add one more status?
If the shipment is not "Last Leg" AND the "Delivery date" is there
then, the status need to show "Not Arrived - DLV presented"

Thank you for your help!!

[Ericwhv]

Hi Fowmy!
It is work, thank you so much!!

appreciate for your help 🙂