Adjust session result based on previous session

[Roym]

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

[Anonymous]

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.

[Roym]

@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. 

[Roym]

@Anonymous Is this something you can help me with? So far I was unable to get this working unfortunatly. Thanks!!

[Anonymous]

Hi @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.

[Roym]

@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

[Anonymous]

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.

[Roym]

@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

[Greg_Deckler]

@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/3395....
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 - __Previous