Forum Discussion
Solved
New Column based on Condition and Calculation
Hello, I am trying to create a line grap showing a "Value" per "Phase" and respective limits. the limits are being calculated based on the previous phase value. The value resulting out of the pha...
Hi PhilipTreacy ,thanks for the quick reply, I'll add more.
Hi Joe89 ,
The Table data is shown below:
Use the following DAX expression to create columns
Low Limit = VAR _phase = [Phase] VAR _Project = [Project] VAR _table = ALL('Table') VAR _result = SWITCH(TRUE(), _phase = 1 , [Value] * 0, _phase = 2 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.5, _phase = 3 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.7, _phase = 4 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 0.9 ) RETURN _resultUpper Limit = VAR _phase = [Phase] VAR _Project = [Project] VAR _table = ALL('Table') VAR _result = SWITCH(TRUE(), _phase = 1 , [Value] * 2, _phase = 2 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.5, _phase = 3 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.3, _phase = 4 , MAXX(FILTER(_table,[Project] = _Project && [Phase] = _phase - 1 ),[Value]) * 1.1 ) RETURN _resultFinal output
Best Regards,
Wenbin Zhou
Hi Joe89
I'm not folloing the logic to create the table of values.
What is the value of Limit? For example Phase 2 => Value P1 +/-50% Limit and in this case, what is P1?
Phil
Quick answer to your question. Value P1 = Value from Phase 1