cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MClipsham92
Frequent Visitor

Specifying a Row using two columns

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:

 

Capture.PNG

 

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,

 

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@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"))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@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"))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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!

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors