Forum Discussion

Icon for Helper III rank

Helper 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
1 year ago
Use CONTAINSSTRING instead.

Icon for Super User rank

Super 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.

Icon for Helper III rank

Helper III

1 year ago

so what is the solution then 🙂 ?

lbendlin
Super 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
  Helper 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
  - lbendlin
    Super User
    1 year ago
    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

Related content