cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Calculate value same column with condition

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.

 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
1 ACCEPTED SOLUTION
Community Support

Thank you for the message, @Anonymous.

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

Best,
Paul

5 REPLIES 5
Anonymous
Not applicable

@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
Not applicable

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

Community Support

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
Not applicable

@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.

Community Support

Thank you for the message, @Anonymous.

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

Best,
Paul

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks