Forum Discussion
Assigning flag based on status value
Hi All,
I am stuck in an issue. I have following data. Here I have a order, its entry id , the staff updating the order status and status.
| OrderID | EntryID | Staff updating | Status updated |
| 101 | 1 | Alex | Ordered |
| 101 | 2 | Ben | Scheduled |
| 101 | 3 | Chris | Absent |
| 101 | 4 | Deren | Cancelled |
| 102 | 5 | Ben | Ordered |
| 102 | 6 | Chris | Scheduled |
| 102 | 7 | Jack | Rescheduled |
| 102 | 8 | Deren | Absent |
| 102 | 9 | Alex | Cancelled |
Now I need to find the staff who scheduled the order and assign it a value 1. If staff A has rescheduled and staff B has scheduled an order then staff A should get value 1 and staff B should get value 0. Here is the output I am expecting:
| Output | Absent Count |
| Alex | 0 |
| Ben | 1 |
| Chris | 0 |
| Derren | 0 |
| Jack | 1 |
Thank you in advance.
Hi Anonymous ,
You could create a measure by the following formula:
flag = VAR _MAXid = MAXX ( FILTER ( 'Table', [Staff updating] = MAX ( [Staff updating] ) ), [EntryID]) VAR _Status = MAXX ( FILTER ( 'Table', 'Table'[EntryID] = _MAXid && [Status updated] IN { "Ordered", "Rescheduled" }),1) RETURN IF ( _Status = 1, 1, 0 )The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous , You need have the order id and name in visual,
You can try a measure like
measure =
var _Rescheduled = calculate(countrows(Table), filter(Table, Table[Status updated] ="Rescheduled"), allexcept(Table, Table[OrderID]))+0
var _scheduled = calculate(countrows(Table), filter(Table, Table[Status updated] ="Scheduled"), allexcept(Table, Table[OrderID]))+0
return
Switch(
_Rescheduled > 0 && max(Table[Status updated]) = "Rescheduled",1,
_scheduled > 0 && max(Table[Status updated]) = "Scheduled",1,
0)Hi Anonymous ,
You could create a measure by the following formula:
flag = VAR _MAXid = MAXX ( FILTER ( 'Table', [Staff updating] = MAX ( [Staff updating] ) ), [EntryID]) VAR _Status = MAXX ( FILTER ( 'Table', 'Table'[EntryID] = _MAXid && [Status updated] IN { "Ordered", "Rescheduled" }),1) RETURN IF ( _Status = 1, 1, 0 )The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
