Complex Measure - Need Help

[ChristianDGreat]

Hello again everyone, and I think I got stucked again.

So I have this table

Order Line #	Timestamp	Status	Flag
1235	1/2/23 8:00 AM	Desk	55
1235	1/3/23 9:00 AM	Inquire	10051
1235	1/4/23 10:00 AM	Book	10051
1235	1/5/23 11:00 AM	Ship	10051
1235	1/6/23 12:00 PM	Invoiced	10051
1236	1/7/23 1:00 PM	Desk	55
1236	1/8/23 2:00 PM	Inquire	10055
1236	1/9/23 3:00 PM	Inquire	10055
1236	1/10/23 4:00 PM	Book	10055
1236	1/11/23 5:00 PM	Book	55
1236	1/12/23 6:00 PM	Ship	10055
1236	1/13/23 7:00 PM	Invoiced	10055
1237	1/7/23 1:00 PM	Desk	55
1237	1/8/23 2:00 PM	Inquire	10055
1237	1/9/23 3:00 PM	Inquire	10055
1237	1/10/23 4:00 PM	Book	10055
1237	1/11/23 5:00 PM	Book	55

The result I'm looking for is this in a Matrix

Order Line #	Flag Skip Order
1235	0
1236	1
1237	1
Total	2

Here's the explanation, so the Flag Skip Order is basically a score,
1 point = after the Status become "Inquire" (the first time), and after that the Flag changed to 55, otherwise its zero (0)

So if you look at the expected result Row 1, OrderLine# 1235 you can see that the Status got inquire at 1/3/23 9am, but after that time the Flag column didnt go back to 55 so the result is 0

Row 2, OrderLine# 1236 got 1 point because when the Status got change to "Inquire" at 1/8/23 2pm, on 1/11/23 5pm the Flag become 55 thus get 1 point.

Basically the tricky part is to how to scan by Order Line #, to check after the first Status become "Inquire", it will look if the Flag become = 55

[Anonymous · Accepted solution]

Hi @ChristianDGreat ,

You can create the measures as below to get it, please find the details in the attachment.

Measure = 
VAR _selorderline =
    SELECTEDVALUE ( 'Table'[Order Line #] )
VAR _mininquiredtime =
    CALCULATE (
        MIN ( 'Table'[Timestamp] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Order Line #] = _selorderline
                && 'Table'[Status] = "Inquire"
        )
    )
VAR _next55dtime =
    CALCULATE (
        MIN ( 'Table'[Timestamp] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Order Line #] = _selorderline
                && 'Table'[Timestamp] > _mininquiredtime
                && 'Table'[Flag] = 55
        )
    )
RETURN
    IF ( ISBLANK ( _next55dtime ), 0, 1 )

Flag Skip Order = SUMX(VALUES('Table'[Order Line #]),[Measure])

Best Regards