DAX Logical statement and filter logic

[Anonymous]

Hello to everyone i have to solve this problem and i dont' konw how to start. 

Basicly i ve  created a calculed column if setting if  order il fullfilled or not but with a specific rule explained below:

I have two table involved:

Table name

 called : order_dtl 

in the tableorder_dtl   i have the single items [order_line] that merge   built an order [order_ID]

Table order_dtl 

Field 

Order_Id	Order_Line                                   (items)	Status ofoperation	IsFullfilled_notFullfilled
1	a1	ship
1	a2	ship
1	a3	ship
2	a4	ship
2	a5	waiting
2	a6	waiting
3	a7	ship
3	a8	waiting

Now basicly i want create a calculed column named Isfullfiled_notfullfilled where if for each order ID all the relative  status operation are ship insert Ship but if just one of that are witing wrote for everyone not fullfilled. 

The result that i expected is the following table 

Order_Id	Order_Line                                             ( items)	Status of operation	IsFullfilled_notFullfilled
1	a1	ship	ship
1	a2	ship	ship
1	a3	ship	ship
2	a4	ship	notship
2	a5	ship	notship
2	a6	waiting	notship
3	a7	ship	notship
3	a8	waiting	notship

i am junior in DAX and i don't know how to set the calculed column "IsFullfilled_notFullfilled" in order to obtain the result above using if condition.

My final achive should be mesure counting  the number of fullfilled e not fullfilled based on the calculed column [IsFullfilled_notFullfilled]

Thanks so much in advace for your help, i really appreciate it 

Regard 

John 

[lbendlin]

So basically for each order you want to find out if at least one order line is still in "waiting" status which means the order is not completely fulfilled.

Do you want to try to find the DAX for this yourself, or would you like some guidance?

[Anonymous]

Yes,  for each order i  want to find out if at least one order line is still in "waiting" status which means the order is not completely fulfilled and in this case every order line must be cosiderated as "waiting" in the calculed column while if every order line is fullfilled also the order is fullfilled. 

I don't know firstly if is possible create a calculated column with this conditions and as second the relative DAX expression, i try to start with a formula like

If [Status of operation]="shipped", "fullfilled", "waiting"  ==> but i don't know how group that with the condition     "if at least one order line is still in "waiting" status it means that the  order is not completely fulfilled and in this case every order line must be cosiderated as "waiting" in the calculed column while if every order line is fullfilled also the order is "fullfilled".

Really it is a nighmare is you can help me, i really appreciate

[lbendlin]

Don't despair - nearly there. I made you confirm the business statement to make it easier to see how to translate that into DAX.

For each order, count the rows in "waiting" status. If that count is zero then the order is "fulfilled", otherwise it is not .

Pseudo code for a calculated column 

fulfillmentstatus= 

var o =selectedvalue(order)

return if(countrows(filter(orderstable,order = o && status="waiting)),"Fulfilled,"waiting")