Forum Discussion

DorisCMoore's avatar
DorisCMoore
Frequent Visitor
3 years ago
Solved

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

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
Need Help
  • andhiii079845's avatar
    andhiii079845
    3 years ago

    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. 

5 Replies

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      DorisCMoore
      Frequent Visitor
      3 years ago

      Works like a charm, thank you so much!! ðŸ˜Š

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Use a VAR and RETURN construct:

    Mymeasure = 

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

    Var *The WI BUG* 

    Return iif() 

    • DorisCMoore's avatar
      DorisCMoore
      Frequent Visitor
      3 years ago

      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?