Power BI - Dax (Funnel)

[Ericwhv]

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	Master	Consol Lane	Movement Lane	GNR Origin	GNR Dest	LegCheck	ArrivalCheck	COB
412647	AMST2606	AMS-CPH	JHB-STO	AMS	STO	First Leg	Not Arrived	17/08/2022 22:00
412647	CPHT0071	CPH-STO	JHB-STO	AMS	STO	Last Leg	Not Arrived
414467	LHRT0639	LHR-BRS	NGB-BHX	AMS	BHX	Transit Leg	Not Arrived, Missing last leg	21/04/2022 02:24
414467	AMST2518	AMS-LHR	NGB-BHX	AMS	BHX	First Leg	Not Arrived, Missing last leg	18/04/2022 23:26
415828	BFST0039	BFS-LHR	BFS-GRU	BFS	FRA	First Leg	Not Arrived	22/04/2022 16:50
415828	LHRT0640	LHR-FRA	BFS-GRU	BFS	FRA	Transit Leg	Not Arrived, Missing Last Leg

the result will require as below

House	Master	Consol Lane	Movement Lane	GNR Origin	GNR Dest	LegCheck	ArrivalCheck	COB
412647	CPHT0071	CPH-STO	JHB-STO	AMS	STO	Last Leg	Not Arrived
415828	LHRT0640	LHR-FRA	BFS-GRU	BFS	FRA	Transit Leg	Not Arrived, Missing Last Leg
414467	LHRT0639	LHR-BRS	NGB-BHX	AMS	BHX	Transit Leg	Not Arrived, Missing last leg	21/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!

[lbendlin]

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]

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	Master	Consol Lane	Movement Lane	GNR Origin	GNR Dest	LegCheck	ArrivalCheck	COB
412647	CPHT0071	CPH-STO	JHB-STO	AMS	STO	Last Leg	Not Arrived
415828	LHRT0640	LHR-FRA	BFS-GRU	BFS	FRA	Transit Leg	Not Arrived, Missing Last Leg
414467	LHRT0639	LHR-BRS	NGB-BHX	AMS	BHX	Transit Leg	Not Arrived, Missing last leg	21/04/2022 02:24

Thank you for your help!!

[lbendlin]

You lost me. What needs changing?

[Ericwhv]

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")

This part, if "Red" change it to the House#?

the "black" is the House#, i dont want to show it in there.

(just want to avoid using the filter, lol)

thank you

[lbendlin]

Instead of using the measure for conditional formatting you want to apply it as a filter for the visual?

[Ericwhv]

Yes

in deed, i am not changing the colour but want to show the result in the visual, thankyou

[lbendlin · Accepted solution]

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

[Ericwhv]

Thank you Ibendlin!
yes, that is one of the way to solve my problem, thank you!!