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.

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

what happened to the ORs ?  Your filter needs to say  II,  not &&

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"))