Assigning flag based on status value

[AdityaPowerBI]

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.

[v-yalanwu-msft · Accepted solution]

Hi @AdityaPowerBI  , 

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.  

[amitchandak]

@AdityaPowerBI , 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)