Forum Discussion

ChristianDGreat's avatar
ChristianDGreat
Resolver I
3 years ago
Solved

Complex Measure - Need Help

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

 

So I have this table

Order Line #TimestampStatusFlag
12351/2/23 8:00 AMDesk55
12351/3/23 9:00 AMInquire10051
12351/4/23 10:00 AMBook10051
12351/5/23 11:00 AMShip10051
12351/6/23 12:00 PMInvoiced10051
12361/7/23 1:00 PMDesk55
12361/8/23 2:00 PMInquire10055
12361/9/23 3:00 PMInquire10055
12361/10/23 4:00 PMBook10055
12361/11/23 5:00 PMBook55
12361/12/23 6:00 PMShip10055
12361/13/23 7:00 PMInvoiced10055
12371/7/23 1:00 PMDesk55
12371/8/23 2:00 PMInquire10055
12371/9/23 3:00 PMInquire10055
12371/10/23 4:00 PMBook10055
12371/11/23 5:00 PMBook55

 

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

Order Line #Flag Skip Order
12350
12361
12371
Total2

 

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



Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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