Forum Discussion
Adjust session result based on previous session
Hi Roym
I restored your data and created a calculated column .
Column =
var _previosid=MAXX(filter('Table','Table'[Test]=EARLIER('Table'[Test])&& 'Table'[ID]<EARLIER('Table'[ID])),'Table'[ID])
var _review=CALCULATE(MAX('Table'[Review]),FILTER('Table','Table'[ID]=_previosid))
return
SWITCH(TRUE(),'Table'[Test]="Effective" && 'Table'[Review]="Effective","Effective",
'Table'[Test]="Effective" && 'Table'[Review]="Ineffective","Ineffective",
'Table'[Test]="Effective" && 'Table'[Review]="Open",_review)
When 'Table'[Review]=”Open”, return value is the previous 'Table'[Review], so you need a var to return the value , in my formula, _review is the var . Then use SWITCH() to judge the 'Table'[Test] and 'Table'[Review].
The final result is as shown :
I have attached my pbix file , you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous Great, thanks!!! This really helps. One last question of something I'm not fully sure about. In this case you have a unique session/id. But with the actual data I have something like this table, should I adjust something in the calculation to get to this?
Session Test Review New_Result Session 1 Effective Effective Effective Session 1 Effective Open Effective Session 1 Effective Ineffective Ineffective (Review is prefered to calculate the new result) Session 1 Effective Open Ineffective Session 2 Effective Effective Effective Session 2 Effective Open Effective Session 2 Effective Ineffective Ineffective (Review is prefered to calculate the new result) Session 2 Effective Open Ineffective
