Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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