Specifying a Row using two columns

[MClipsham92]

Hi All,

Apologies if the title wasn't very clear I was having trouble thinking of how to word it.

I have some survey data from students from multiple points in a programme. Each survey the students complete they are rated from 1-5 in a category. I'm trying to write a conditional column that will be either "Improved", "Stayed the Same" or "Not Improved" for each category. I had this working as an IF statement but have recently unpivoted the data in order to get line graphs to work and now can't figure out how to write this.

The unpivoted data looks like this:

There is also a column called Name.1 that I've not included because it contains individual student names.

My previous column was a series of nested IF statements that basically went IF (Resilience Start of Programme > Resilience Week 12), "Did not Improve",IF Etc Etc

Any help would be great.

Thanks,

[ryan_mayu · Accepted solution]

@MClipsham92 

Hope I understand your requirement clearly. Thanks.

Column = 
VAR a= if(Sheet3[attribute]="listening end of week 12",MAXX(FILTER(Sheet3,Sheet3[student]=EARLIER(Sheet3[student])&&Sheet3[attribute]="resilience start of programme"),Sheet3[score]))
return if(ISBLANK(a),BLANK(),if(Sheet3[score]<a,"Did not improve","Improved"))

[MClipsham92]

I've no idea how I'd go about writing that myself but it seems to have worked with a tiny bit of tweaking, I've ended up with this: 

Listening Improved Term 1 = VAR a= IF(Instructor[Attribute]="listening End of Week 12",MAXX(FILTER(Instructor,Instructor[Name.1]=EARLIER(Instructor[Name.1])&&Instructor[Attribute]="Listening Start of Programme"),Instructor[Score]))
return if(ISBLANK(a),BLANK(),if(Instructor[Score]<a,"Did not improve","Improved"))

This works well but how would I go about adding in a "Stayed the same" result if the score are the same? (currently counting as "Improved". Is it a case of editing the if statement at the end? 

Also an explanation of how it works so I could to learn to write similar myself in the future would be amazing, or maybe a link to an article discussing it? 

Thanks!

[ryan_mayu]

@MClipsham92 

if(ISBLANK(a),BLANK(),if(Instructor[Score]<a,"Did not improve",if(score=a,"Stay the same","Improved"))

You can add another if statement in the place I highlighted in red.

I think the key point is about Earlier formular.

https://docs.microsoft.com/en-us/previous-versions/ff452135(v=technet.10)

Thanks and BR

Ryan