Forum Discussion

PC2022's avatar
PC2022
Icon for Helper III rankHelper III
1 year ago
Solved

CONTAINS formula or something else?

I need to calculate sum score based on criteria that all 5 review phases evaluations are present for the project ID. For example, for the project below, the score shouldn't get calculated, as only 2 ...
  • lbendlin's avatar
    lbendlin
    1 year ago

    Use CONTAINSSTRING instead.

PC2022's avatar
PC2022
Icon for Helper III rankHelper III
1 year ago

all 5 evaluations need to be there, so i need to use "and" &&

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

Logical operations are the other way round from casual speech.  Your condition will ONLY be true if ALL the percentage strings are included in [Review Phase] TOGETHER.

  • PC2022's avatar
    PC2022
    Icon for Helper III rankHelper III
    1 year ago

    so what is the solution then 🙂 ?

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      1 year ago

      put the ORs back in.

       

      EvalJobComp = CALCULATE(SUMX(crb87_consultantevaluation,
      ([EvalDesignComp30] * 0.7) + ([EvalConstrComp] * 0.3)),
      CONTAINSSTRING(crb87_consultantevaluation[Review Phase], "30% Design") ||
      CONTAINSSTRING(crb87_consultantevaluation[Review Phase], "60% Design") ||
      CONTAINSSTRING( crb87_consultantevaluation[Review Phase], "100% Design") ||
      CONTAINSSTRING( crb87_consultantevaluation[Review Phase], "Mid-Construction") ||
      CONTAINSSTRING( crb87_consultantevaluation[Review Phase], "Construction/Final"))
      • PC2022's avatar
        PC2022
        Icon for Helper III rankHelper III
        1 year ago

        maybe i am not explaining it correctly - it can't be OR. 30, 60, 100, mid and final eval must be there for EvalJobComp score