cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
milenamr2 Regular Visitor
Regular Visitor

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

Accepted Solutions
V-pazhen-msft Established Member
Established Member

Re: Calculate value same column with condition

Thank you for the message, @vimal_parmar

 

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
vimal_parmar Senior Member
Senior Member

Re: Calculate value same column with condition

@milenamr2 

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)
milenamr2 Regular Visitor
Regular Visitor

Re: Calculate value same column with condition

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

 

V-pazhen-msft Established Member
Established Member

Re: Calculate value same column with condition

Hi, @milenamr2 

 

and Thank you @vimal_parmar .

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

 

 

 

 

 

 

 

Best,

Paul

vimal_parmar Senior Member
Senior Member

Re: Calculate value same column with condition

@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 Established Member
Established Member

Re: Calculate value same column with condition

Thank you for the message, @vimal_parmar

 

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
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)