Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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