topic Re: CONTAINS formula or something else? in DAX Commands and Tips

CONTAINS formula or something else?

PC2022 — Tue, 22 Apr 2025 18:06:55 GMT

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.

Re: CONTAINS formula or something else?

lbendlin — Tue, 22 Apr 2025 18:52:14 GMT

Use CONTAINSSTRING instead.

Re: CONTAINS formula or something else?

PC2022 — Tue, 22 Apr 2025 19:06:26 GMT

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

Re: CONTAINS formula or something else?

lbendlin — Tue, 22 Apr 2025 19:23:08 GMT

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

Re: CONTAINS formula or something else?

PC2022 — Tue, 22 Apr 2025 19:37:21 GMT

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

Re: CONTAINS formula or something else?

lbendlin — Tue, 22 Apr 2025 19:47:24 GMT

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.

Re: CONTAINS formula or something else?

PC2022 — Tue, 22 Apr 2025 19:53:14 GMT

so what is the solution then 🙂 ?

Re: CONTAINS formula or something else?

lbendlin — Tue, 22 Apr 2025 19:58:47 GMT

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

Re: CONTAINS formula or something else?

PC2022 — Tue, 22 Apr 2025 20:20:06 GMT

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

Re: CONTAINS formula or something else?

lbendlin — Tue, 22 Apr 2025 20:21:30 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523