Forum Discussion
Adjust session result based on previous session
I have a table in PBI that shows a long list of assessment results, both the test and review, and based on that I need to calculate the New_Result. As example I have the below table. For session 1 and session 2 in the below table I have the New_Result figgerd out.
For session 1 the New_Result is Effective because the test is effective and the review is open, AND the previous review is effective
For session 2 the result is ineffective because the Review is ineffective (Review is always prefered above the test result)
But the part I would need a bit of assistance/guidance on is session 3.
Because the session before (session 2) has in ineffective review it should show the ineffective result as New_Result and not effective. Because the previous Review was ineffective. It then doesn't matter what the test result is. It should always show as Ineffective.
| Session | Test | Review | New_Result |
| Session 0 | Effective | Effective | Effective |
| Session 1 | Effective | Open | Effective |
| Session 2 | Effective | Ineffective | Ineffective (Review is prefered to calculate the new result) |
| Session 3 | Effective | Open | Ineffective |
8 Replies
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
Roym So, seems like you need to essentially look at previous row when looking to calculate a value for the current row. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousHi Roym
Unique session/id is necessary , because you mentioned , if test=” Effective” and review=” Open” , the value of new result will return the previous review . But when you have multiple identical values，how do you judge the previous review ? So the unique value becomes the criterion for judging the order of precedence.
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 There is an unique identifier available like I now have in the below table. Would it be possible this way?
Session ID Test Review New_Result Session 1 20 Effective Effective Effective Session 1 21 Effective Open Effective Session 1 22 Effective Ineffective Ineffective (Review is prefered to calculate the new result) Session 1 23 Effective Open Ineffective Session 2 41 Effective Effective Effective Session 2 42 Effective Open Effective Session 2 43 Effective Ineffective Ineffective (Review is prefered to calculate the new result) Session 2 44 Effective Open Ineffective
Hi Roym
Of course you can, you can use my formula to try it out.
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 I just tried it with some additional data and I didn't get it fully working correctly (yet). Hopefully you can give me some additional guidance. In the below screenshot (same report as you shared) I have some reall data. For most of them it going correctly but the change from session1 to session2 is not working correcltly I think. The first row related to session2 has an effective test and open review. Therefore it should be marked as effective as it is the first row related to session2.
Anonymous Is this something you can help me with? So far I was unable to get this working unfortunatly. Thanks!!
