Forum Discussion
Helper IIIHelp with my code - Seems correct but data being pulled is incorrect
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
Helper IIIHI 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 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.
Helper IIIAlso 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.