How to dynamically modify a Measure result based on a specific value?

[DorisCMoore]

Hello,

I'm trying to create a Measure, which calculation should be dynamically impacted based on a column value.

To illustrate, here's the "default" mesure I'm using:

Measure = CALCULATE(sum(AllWorkItems[Effort]),AllWorkItems[State]="Done")

Simply put, if my State column shows "Done" value, then I want to calculate the Sum for my Effort column.

On top of that, I have a [WI] column, and I would like the result of this measure to vary according to the WI value (add 100 to the result if WI is a Bug, or just keep the result as it is for any other value)

Code equivalent would be something like that:

If State = "Done" then

If WI = "Bug" then

Sum(Effort) + 100

else

Sum(Effort)

End if

end if

So far my workaround was to duplicate the measure.

Is it possible to achieve the same result through one single measure?

Thank you,

Doris

[andhiii079845 · Accepted solution]

In which context do you want your measure? Can you please make this more clear for me, please?
You can try this:

Measure = IF(MAX('Table'[Wi])="Bug",CALCULATE(sum('Table'[Effort]),'Table'[State]="Done")+100,CALCULATE(sum('Table'[Effort]),'Table'[State]="Done"))

Be aware of the measure you use sum instead of sumx. 

[DorisCMoore]

Works like a charm, thank you so much!! 😊

[andhiii079845]

Do you have some example data? 

[andhiii079845]

Use a VAR and RETURN construct:

Mymeasure = 

Var _firstmeasure = CALCULATE(sum(AllWorkItems[Effort]),AllWorkItems[State]="Done")

Var *The WI BUG* 

Return iif() 

[DorisCMoore]

Thank you for the proposition but var WIbug = AllWorkItems[WI] = "Bug" is probably what bothers me since the begining. As WI is a string value, I'm always gettig a "cannot find name" kind of error.

Maybe this is how I should have put the question: how can we manipulate strings in a measure?