Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!




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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.