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

Used Containsstring. Doesn't give error anymore but not returning any results although i know there are projects where all phases evals exist.

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