cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

Could you please help me using DAX?

 

Work Item IdValueState
111025In Progress
1169Done
11 New
8737 New
87372Approved
87371Prioritized
87375In Progress
873714Done
1 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

 

View solution in original post

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 IdStateValue
11Done1025
11In Progress1025
11New1025
8737Approved5
8737Done5
8737In Progress5
8737New5
8737Prioritized5

 

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.

Thank you for the message, @Anonymous. 

 

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

 

Best, 
Paul

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors