Forum Discussion

Joe89's avatar
Joe89
Regular Visitor
2 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 _result
    Upper 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 _result

    Final output

     

    Best Regards,
    Wenbin Zhou

Joe89's avatar
Joe89
Regular Visitor
2 years ago

Hello Phil,

 

and thanks for taking the time to read!

 

Indeed the limit values are based on the previous phase value (except for P1 where it keeps its value)

Another example:

ProjectA has the Phase and Values as shown below. Where Phase is the project progress and value for example its investment needed.

Phase 1: Value 10, as there is no previous phase it will take its own value 10 (however this phase 1 boundary is optional / not the core issue)

-> Lower Limit in PDP1 = -100% so the value is 0 for Lower Limit

-> Upper Limit in PDP1 = +100% so the value is 20 for Upper Limit

Phase 2: Value 15 of this phase. But for the limit it takes the previous phase value of 10

-> Lower Limit in PDP2 = -50% so the value is 5 for Lower Limit

-> Upper Limit in PDP2 = +50% so the value is 15 for Upper Limit

Phase 3: Value is 10 (again, this one is not following any logic it is a given quantity), but it takes the previous phase value of 15 for the limits.

-> Lower Limit in PDP3 = -30% so the value is 10.5 for Lower Limit

-> Upper Limit in PDP3 = +30% so the value is 19.5 for Upper Limit

 

Hope this helps to explain it.

The problem I am facing revolves mainly around taking that value for a project and its phase, changing it via a calulcation. And then putting it into the next phase as the upper / lower limit. 

I tried something of the likes of looking up the project name, an if clause to the phase with a calculation and then return this value to a variable which is then set as the value for the next phase limit. However this did not work and returned 0 for the next phase value all the time.

 

For some context. I am trying to create a graph based on the recorded data of cost and phase for projects to see if their increase or decrease in budget exceeded the threshold set by the phase. The further the project progresses in phases, the more mature it is and the smaller these limits for changes in budget become.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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 _result
    Upper 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 _result

    Final output

     

    Best Regards,
    Wenbin Zhou