Forum Discussion
Solved
Help with my code - Seems correct but data being pulled is incorrect
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
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
4 Replies
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,
LydiaTHanks 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!!!
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 8)
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 OGLObviously 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.
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.
