Forum Discussion

Ericwhv's avatar
Ericwhv
Icon for Helper II rankHelper II
4 years ago
Solved

Power BI - Dax (Funnel)

Hi Everyone

appreciate for your help in advance.

 

i am building a dashboard to show the most recent shipment details

for below table, if i would like to show the HOUSE in red on the dashboard, how can i do that?

 

Criteria:

if we have a Last Leg at LegCheck column, then, pick the row of "house" of the Last leg

if we do not have a last leg, first, pick the row which is no COB in place

and then, pick the row has a most recent COB date

 

the data as below

House

MasterConsol LaneMovement LaneGNR OriginGNR DestLegCheckArrivalCheckCOB
412647AMST2606AMS-CPHJHB-STOAMSSTOFirst LegNot Arrived17/08/2022 22:00
412647CPHT0071CPH-STOJHB-STOAMSSTOLast LegNot Arrived 
414467LHRT0639LHR-BRSNGB-BHXAMSBHXTransit LegNot Arrived, Missing last leg21/04/2022 02:24
414467AMST2518AMS-LHRNGB-BHXAMSBHXFirst LegNot Arrived, Missing last leg18/04/2022 23:26
415828BFST0039BFS-LHRBFS-GRUBFSFRAFirst LegNot Arrived22/04/2022 16:50
415828LHRT0640LHR-FRABFS-GRUBFSFRATransit LegNot Arrived, Missing Last Leg 

 

the result will require as below

House

MasterConsol LaneMovement LaneGNR OriginGNR DestLegCheckArrivalCheckCOB
412647CPHT0071CPH-STOJHB-STOAMSSTOLast LegNot Arrived 
415828LHRT0640LHR-FRABFS-GRUBFSFRATransit LegNot Arrived, Missing Last Leg 
414467LHRT0639LHR-BRSNGB-BHXAMSBHXTransit LegNot Arrived, Missing last leg21/04/2022 02:24

 

Reason:

412647 - because it is last leg (COB could null or data in there)

415828 - there are no last leg, show the one with empty COB

414467 - there are no last leg, compare the COB, choose the most recent one (21/04 vs 18/04)

 

Thank you every one!

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Add the measure to the visual filters and set it to "red"

8 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Not sure if I captured the logic behind your rules.

     

    Font Color = 
var b = CALCULATETABLE('Table',ALLEXCEPT('Table','Table'[House]))
var l = countrows(filter(b,'Table'[LegCheck]="Last Leg"))
var c = countrows(filter(b,isblank('Table'[COB])))
return 
switch(TRUE()
,max('Table'[LegCheck])="Last Leg" && l>0,"red"
,isblank(max('Table'[COB])) && c >0,"red"
,l=0 && c=0 && max('Table'[COB])=maxx(b,[COB]),"red"
,"black")

     

    see attached

    • Ericwhv's avatar
      Ericwhv
      Icon for Helper II rankHelper II
      4 years ago

      Hi #lbendlin

      Thank you for your reply

      the logic is right!


      if it could change it from the "Colour" to the HB#?

       

      the result I hope is....

      House

      		MasterConsol LaneMovement LaneGNR OriginGNR DestLegCheckArrivalCheckCOB
      412647CPHT0071CPH-STOJHB-STOAMSSTOLast LegNot Arrived 
      415828LHRT0640LHR-FRABFS-GRUBFSFRATransit LegNot Arrived, Missing Last Leg 
      414467LHRT0639LHR-BRSNGB-BHXAMSBHXTransit LegNot Arrived, Missing last leg21/04/2022 02:24

       

      Thank you for your help!!