Calculate value same column with condition

[Anonymous]

Hi all!

I need to calculate the value of the state "in progress" for each ID by checking if the state column has "in progress" and "prioritized", otherwise I will not return anything. In this case below should return to ID 11 = 0 and ID 8737 = 5.

Could you please help me using DAX?

Work Item Id	Value	State
11	1025	In Progress
11	69	Done
11		New
8737		New
8737	2	Approved
8737	1	Prioritized
8737	5	In Progress
8737	14	Done

[Anonymous]

@Anonymous 

Measure = 
VAR _state = CALCULATE(CONCATENATEX('Table','Table'[State]," "),ALLEXCEPT('Table','Table'[Work Item Id]))
VAR _value= CALCULATE(SUM('Table'[Value]),'Table'[State]="In Progress")
RETURN IF((SEARCH("In Progress",_state,1,0)+SEARCH("Prioritized",_state,1,0))>=2,_value,0)

[Anonymous]

Unfortunately it only worked for id 8737 which has both states (prioritized and in progress), but in id 11 it should show 0 since it only has in progress. Is it possible?

Thank you!!

Work Item Id	State	Value
11	Done	1025
11	In Progress	1025
11	New	1025
8737	Approved	5
8737	Done	5
8737	In Progress	5
8737	New	5
8737	Prioritized	5

[V-pazhen-msft]

Hi, @Anonymous 

and Thank you @Anonymous .

It should work properly with this DAX formula, you can try again or maybe figure around with the table visual.

Best,

Paul

[Anonymous]

@V-pazhen-msft Not able to see your formula 
But in above code instead of both of the search output greater than two each search output should be greater than zero.

[V-pazhen-msft · Accepted solution]

Thank you for the message, @Anonymous. 

RETURN IF((SEARCH("In Progress",_state,1,0) && SEARCH("Prioritized",_state,1,0)>0), _value,0) 

Best, 
Paul