Help with my code - Seems correct but data being pulled is incorrect

[Ayupchap]

Hey all

OK so I am writing this rather large piece of code which works in a custom column.  It worked for the first part and seemed to be error free with the rest but it now seems I was mistaken and the code isn't doing what its meant correctly.  The answers I get do not equate to the code I've written so I must have made a mistake but for th elife of me have no idea what it could be.  I am using a data pull from Postgre via an OBDC connection.

Heres the code:

if([grade_level_at_time_of_test]="KG" and [assessment_name]="17-18 FP Round 1" and 
[independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D" or [independent_level]="E" or [independent_level]="F" or [independent_level]="G" or [independent_level]="H" or [independent_level]="I" or [independent_level]="J" or [independent_level]="K" or [independent_level]="L" or [independent_level]="M" or [independent_level]="N" or [independent_level]="O" or [independent_level]="P" or [independent_level]="Q" or [independent_level]="R" or [independent_level]="S" or [independent_level]="T" or [independent_level]="U" or [independent_level]="V" or [independent_level]="W" or [independent_level]="X" or [independent_level]="Y" or [independent_level]="Z" or [independent_level]="Z+")
then "AGL" 

else if(
[grade_level_at_time_of_test]="KG" and [assessment_name]="17-18 FP Round 1" and [independent_level]="Pre-A")
then "OGL"

else if(
[grade_level_at_time_of_test]="1" and [assessment_name]="17-18 FP Round 1" and [independent_level]="Pre-A")
then "BBGL"

else if(
[grade_level_at_time_of_test]="1" and [assessment_name]="17-18 FP Round 1" and
[independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D")
then "BGL"

else if(
[grade_level_at_time_of_test]="1" and [assessment_name]="17-18 FP Round 1" and [independent_level]="E")
then "OGL"

else if(
[grade_level_at_time_of_test]="1" and [assessment_name]="17-18 FP Round 1" and
[independent_level]="F" or [independent_level]="G" or [independent_level]="H" or [independent_level]="I" or [independent_level]="J" or [independent_level]="K" or [independent_level]="L" or [independent_level]="M" or [independent_level]="N" or [independent_level]="O" or [independent_level]="P" or [independent_level]="Q" or [independent_level]="R" or [independent_level]="S" or [independent_level]="T" or [independent_level]="U" or [independent_level]="V" or [independent_level]="W" or [independent_level]="X" or [independent_level]="Y" or [independent_level]="Z" or [independent_level]="Z+")
then "AGL"

else if(
[grade_level_at_time_of_test]="2" and [assessment_name]="17-18 FP Round 1" and
[independent_level]="Pre-A" or [independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D" or [independent_level]="E")
then "BBGL"

else if(
[grade_level_at_time_of_test]="2" and [assessment_name]="17-18 FP Round 1" and [independent_level]="K")
then "OGL"

else if(
[grade_level_at_time_of_test]="2" and [assessment_name]="17-18 FP Round 1" and
[independent_level]="L" or [independent_level]="M" or [independent_level]="N" or [independent_level]="O" or [independent_level]="P" or [independent_level]="Q" or [independent_level]="R" or [independent_level]="S" or [independent_level]="T" or [independent_level]="U" or [independent_level]="V" or [independent_level]="W" or [independent_level]="X" or [independent_level]="Y" or [independent_level]="Z" or [independent_level]="Z+")
then "AGL"

else if(
[grade_level_at_time_of_test]="3" and [assessment_name]="17-18 FP Round 1" and
[independent_level]="Pre-A" or [independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D" or [independent_level]="E" or [independent_level]="F" or [independent_level]="G" or [independent_level]="H" or [independent_level]="I" or [independent_level]="J" or [independent_level]="K")
then "BBGL"


else if(
[grade_level_at_time_of_test]="3" and [assessment_name]="17-18 FP Round 1" and
[independent_level]="O" or [independent_level]="P" or [independent_level]="Q" or [independent_level]="R" or [independent_level]="S" or [independent_level]="T" or [independent_level]="U" or [independent_level]="V" or [independent_level]="W" or [independent_level]="X" or [independent_level]="Y" or [independent_level]="Z" or
[independent_level]="Z+")
then "BBGL"

else if(
[grade_level_at_time_of_test]="4" and [assessment_name]="17-18 FP Round 1" and
[independent_level]="Pre-A" or [independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D" or [independent_level]="E" or [independent_level]="F" or [independent_level]="G" or [independent_level]="H" or [independent_level]="I" or [independent_level]="J" or [independent_level]="K" or [independent_level]="L" or [independent_level]="M" or [independent_level]="N")
then "BBGL"


else null

[v-yuezhe-msft · Accepted solution]

@Ayupchap,

Please check if the following code returns your expected result. If not, please share sample data of your table and describe your desired result.

if([grade_level_at_time_of_test]="KG" and [assessment_name]="17-18 FP Round 1" and 
([independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D" or [independent_level]="E" or [independent_level]="F" or [independent_level]="G" or [independent_level]="H" or [independent_level]="I" or [independent_level]="J" or [independent_level]="K" or [independent_level]="L" or [independent_level]="M" or [independent_level]="N" or [independent_level]="O" or [independent_level]="P" or [independent_level]="Q" or [independent_level]="R" or [independent_level]="S" or [independent_level]="T" or [independent_level]="U" or [independent_level]="V" or [independent_level]="W" or [independent_level]="X" or [independent_level]="Y" or [independent_level]="Z" or [independent_level]="Z+"))
then "AGL" 

else if(
[grade_level_at_time_of_test]="KG" and [assessment_name]="17-18 FP Round 1" and [independent_level]="Pre-A")
then "OGL"

else if(
[grade_level_at_time_of_test]="1" and [assessment_name]="17-18 FP Round 1" and [independent_level]="Pre-A")
then "BBGL"

else if(
[grade_level_at_time_of_test]="1" and [assessment_name]="17-18 FP Round 1" and
([independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D"))
then "BGL"

else if(
[grade_level_at_time_of_test]="1" and [assessment_name]="17-18 FP Round 1" and [independent_level]="E")
then "OGL"

else if(
[grade_level_at_time_of_test]="1" and [assessment_name]="17-18 FP Round 1" and
([independent_level]="F" or [independent_level]="G" or [independent_level]="H" or [independent_level]="I" or [independent_level]="J" or [independent_level]="K" or [independent_level]="L" or [independent_level]="M" or [independent_level]="N" or [independent_level]="O" or [independent_level]="P" or [independent_level]="Q" or [independent_level]="R" or [independent_level]="S" or [independent_level]="T" or [independent_level]="U" or [independent_level]="V" or [independent_level]="W" or [independent_level]="X" or [independent_level]="Y" or [independent_level]="Z" or [independent_level]="Z+"))
then "AGL"

else if(
[grade_level_at_time_of_test]="2" and [assessment_name]="17-18 FP Round 1" and
([independent_level]="Pre-A" or [independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D" or [independent_level]="E"))
then "BBGL"

else if(
[grade_level_at_time_of_test]="2" and [assessment_name]="17-18 FP Round 1" and [independent_level]="K")
then "OGL"

else if(
[grade_level_at_time_of_test]="2" and [assessment_name]="17-18 FP Round 1" and
([independent_level]="L" or [independent_level]="M" or [independent_level]="N" or [independent_level]="O" or [independent_level]="P" or [independent_level]="Q" or [independent_level]="R" or [independent_level]="S" or [independent_level]="T" or [independent_level]="U" or [independent_level]="V" or [independent_level]="W" or [independent_level]="X" or [independent_level]="Y" or [independent_level]="Z" or [independent_level]="Z+"))
then "AGL"

else if(
[grade_level_at_time_of_test]="3" and [assessment_name]="17-18 FP Round 1" and
([independent_level]="Pre-A" or [independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D" or [independent_level]="E" or [independent_level]="F" or [independent_level]="G" or [independent_level]="H" or [independent_level]="I" or [independent_level]="J" or [independent_level]="K"))
then "BBGL"


else if(
[grade_level_at_time_of_test]="3" and [assessment_name]="17-18 FP Round 1" and
([independent_level]="O" or [independent_level]="P" or [independent_level]="Q" or [independent_level]="R" or [independent_level]="S" or [independent_level]="T" or [independent_level]="U" or [independent_level]="V" or [independent_level]="W" or [independent_level]="X" or [independent_level]="Y" or [independent_level]="Z" or
[independent_level]="Z+"))
then "BBGL"

else if(
[grade_level_at_time_of_test]="4" and [assessment_name]="17-18 FP Round 1" and
([independent_level]="Pre-A" or [independent_level]="A" or [independent_level]="B" or [independent_level]="C" or [independent_level]="D" or [independent_level]="E" or [independent_level]="F" or [independent_level]="G" or [independent_level]="H" or [independent_level]="I" or [independent_level]="J" or [independent_level]="K" or [independent_level]="L" or [independent_level]="M" or [independent_level]="N"))
then "BBGL"


else null

Regards,
Lydia

[Ayupchap]

THanks so much Lydia, this is amazing and pulling the results perfectly!!!  I had a few other assessments I needed to code but using your code method here I have a fully working system working amazing!  Thanks so much!!!

[Ayupchap]

HI all

OK so I need to make what I pressume to be a custom formula column to get the results I want.  Its a little tricky to explaion so I am going to try and break it down a little. This is for use with a student assessment that occurs and its a way to grade their performance and then analyze the results as a whole.  The reults 

Basically I have a dataset which dependent on a few entries in the fields I want to have a different code, one of 4  This is going to be called Level and are as follows:

OGL (which stands for on grade level)

AGL (Above grade level)

BBGL (1Yr+ Below Grade Level)

BGL (< 1yr Below Grade Level)

- I do not need the contents of the () to appear just the capitalized code. 

OK so to know which one to use a combination of fields has to be checked these are

Year_Grade (can be KG, 1, 2, 3, 4, 5, 6, 7 or 😎

Test_Name (17-18 FP Round 1, 17-18 FP Round 2, 17-18 FP Round 3, 17-18 FP Round 4, 16-17 FP Round 1, 16-17 FP Round 2, 16-17 FP Round 3, 16-17 FP Round 4)

Letter_Level (Pre-A ,A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z)

So for example if the Grade is KG, the Test_Name is 17-18 FP Round 1 and the Letter_Level is any of the following; A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z - They are AGL

If the Grade is KG, the Test_Name is 17-18 FP Round 1 and the Letter_Level is any of the following; Pre-A - They are OGL

Obviously there are numerous combinations but whats the best way to code this in PowerBI, there is a Level for all possible combination s which I have a list of. I'm aware this could take a minute so any help much appreciate on an efficient way to do this.

[Ayupchap]

Also just to add I also have the full list in a spreadsheet.  It has the fields Grade, Assessment_Title, Level_Letter along with the Level it should get. If this is any help or can be used somehow by being referenced in someway a bit like a vlookup kind of thing.