CONTAINS formula or something else?

[PC2022]

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 phases have been completed. 

EvalJobComp = CALCULATE(SUMX(crb87_consultantevaluation,

([EvalDesignComp30] * 0.7) + ([EvalConstrComp] * 0.3)),

CONTAINS(crb87_consultantevaluation, crb87_consultantevaluation[Review Phase], "30% Design") ||

CONTAINS(crb87_consultantevaluation, crb87_consultantevaluation[Review Phase], "60% Design") ||

CONTAINS(crb87_consultantevaluation, crb87_consultantevaluation[Review Phase], "100% Design") ||

CONTAINS(crb87_consultantevaluation, crb87_consultantevaluation[Review Phase], "Mid-Construction") ||

CONTAINS(crb87_consultantevaluation, crb87_consultantevaluation[Review Phase], "Construction/Final"))

I get an error: a function "contains" has been used in a true/false expression that is used as a table filter expression.

[PC2022]

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]

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

[PC2022]

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

[lbendlin]

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]

so what is the solution then 🙂 ?

[lbendlin]

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]

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin · Accepted solution]

Use CONTAINSSTRING instead.