Forum Discussion

Ericwhv's avatar
Ericwhv
Helper II
4 years ago
Solved

DAX - Measurement support

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"

 

HBICConsol LaneMovement LaneGNR OrignGNR DestArrival dateLeg CheckResult
H4805777AMS1234AMS-FRAAMS-BUHAMSBUH01-AugFirst LegNot arrive
H4805777BUD4567BUD-BUHAMS-BUHAMSBUH Last LegNot arrive
H4805777FRA5678FRA-BUDAMS-BUHAMSBUH03-AugTransit LegNot arrive
H4805792AMS9999AMS-FRAAMS-BUHAMSBUH02-AugFirst LegNot Arrive, Missing last leg
H4805792FRA8888FRA-BUDAMS-BUHAMSBUH Transit LegNot Arrive, Missing last leg
H55778AMS5555AMS-FRAAMS-BUHAMSBUH03-AugFirst LegArrived
H55778FRA6666FRA-BUDAMS-BUHAMSBUH04-AugTransit LegArrived
H55778BUD7777BUD-BUHAMS-BUHAMSBUH05-AugLast LegArrived

 

Thank you every one!!!

Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    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

     










3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    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's avatar
      Ericwhv
      Helper II
      4 years ago

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

      appreciate for your help 🙂

    • Ericwhv's avatar
      Ericwhv
      Helper II
      2 years ago

      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!!