Forum Discussion
CASE Statement to DAX
I was in a hurry and didn't close my parentheses.
MeasureorColumn= SWITCH( Table[Academic Career], "Undergraduate",FORMAT(Table[CGPA UG], "General Number"), "Masters", FORMAT(Table[CGPA GR], "General Number"), "No CGPA" )
So if I understand you correctly, you have 2 columns for different levels of CGPA undergrad and grad, and you want to merge them into one column that shows what was in CGPA UG if they're an undergrad and CGPA GR if they're a grad student. Is that correct?
CGPAMerged = SWITCH( TableName[Academic Career], "Undergraduate", TableName[CGPA UG], "Master's", TableName[CGPA GR], BLANK() )
This would give you a single column that would merge the 2 based on the student's level in column AB in your sample data. The last argument returns blank if their career is anything other than undergraduate or master's. Then a simple measure like
Avg CGPA = AVERAGE(TableName[CGPAMerged])
...would give you the average per plan if you placed it on a chart along with column AC.
I don't know of any reason for that. The new CGPAMerged column you created is on the same table as Academic Plan, just like in the sample data, right? And the measure is an average of that new column?
Anonymous 10,000 thank you's!!! That did it!!!
Now that I have the values in one column labeled as [**bleep** GPA]. Do I have to create another calculated column that takes the values and turns them into integers and treats the "No CGPA" as 0? Is there an approach for this? DAX is a brave new world to me but the more I come across the more I learn.
I'm not sure I understand what you're asking. What is the purpose of this other column?
Just wait until you need to use a SWITCH in a measure instead of a column. It's easy once you understand what's going on but often confusing the first time you try.
Hi Anonymous,
The column we created was a result of our student information system outputting CGPA for students in different columns instead of giving one CGPA column. So CGPA UG would have a CGPA populate if the students academic career level was undergraduate and their CGPA GR column would show as 0. Same thing if they are a graduate student, the CGPA UG column would show as 0 and the CGPA GR column would show a number. I wanted to be able to reflect as an average by degree program so I figured I needed to get CGPA regardless of UG or GR in one column and allow the column for academic program to be the point for which CGPA was pivoted. Each row represents a student.
I created a demo excel file based on the real data so that I don't violate FERPA Law: https://drive.google.com/open?id=0BxvqEMoNpMLiYUl4bjlmRE5scVk
So CGPA UG is presented in column U and CGPA GR is presented in column V. Note that some students have a 0 in CGPA UG and GR. That would be the case if they don't have a CGPA yet because they withdrew from their courses denoted by a W in column Y.
The individuals Academic Career is presented in column AB.
So I figured if I could get CGPA in one column and use column AC I could then say the Average CGPA for students in the MS in Aeronautics is X.XX.
So if I understand you correctly, you have 2 columns for different levels of CGPA undergrad and grad, and you want to merge them into one column that shows what was in CGPA UG if they're an undergrad and CGPA GR if they're a grad student. Is that correct?
CGPAMerged = SWITCH( TableName[Academic Career], "Undergraduate", TableName[CGPA UG], "Master's", TableName[CGPA GR], BLANK() )
This would give you a single column that would merge the 2 based on the student's level in column AB in your sample data. The last argument returns blank if their career is anything other than undergraduate or master's. Then a simple measure like
Avg CGPA = AVERAGE(TableName[CGPAMerged])
...would give you the average per plan if you placed it on a chart along with column AC.
Thank you Anonymous. That worked perfectly. I would never have got the SWITCH thing. totally a new language for me.
Anonymous I don't know whether to be optimistic or afraid!
The below is the dashboard that all of this work helped create. I am assuming the measure created to get the average CGPA is the reason why when selecting an Academic Plan from the filters at the top is what keeps the CGPA from changing with the rest of the information?
the CGPA visual is created using Card.
According to the structure you showed in your sample data that card should change when you select something in that Academic Plan slicer. Check visual interactions and make sure you don't have interactions turned off between that card and that slicer.
I checked and the interaction is turned on.
And no matter what you select in that slicer, it still says the average is 3.30?
Yes, thats correct. I rechecked all of the slicers and they affect all other visuals presented in the dashboard. I don't see anything special that would stop it from also impacting the Card.
I don't know of any reason for that. The new CGPAMerged column you created is on the same table as Academic Plan, just like in the sample data, right? And the measure is an average of that new column?
Yes, thats correct. I am only working from one table. Possibly a bug in Power BI?
I don't think so. There's something we're missing here. Have you run through that new column to make sure that it has the correct values?
Just did a quick scan through and it grab the CGPA from the correct column to add to the new column. Its formatted as a Decimal Number. None of the cells are blank.
It has to be visual interactions then, somehow. Can I see a screenshot of them?
Sure see below. When I change to a table and put the academic plans on the rows and the AVG CGPA on the Column it does show the Average CGPA by program.
What if you just change the card to a table visual and don't put anything else on it? Just leave everything else alone. Does the slicer change it then?
Correct, I just changed to a table and didn't do anything. I also tried Matrix. It seems that anything we do concerning the MergedCGPA column and the Average of that don't change with everything else in the dashboard based on the Slicer selection.
There has to be something else about this data model that I can't see that's causing this. At this point I don't know what else to suggest without seeing the actual pbix file myself.
FERPA Law stands in front of me and completion! I will keep playing with it to see if I can find anything that stands out. The column is grabbing the correct number. They are all formatted the same as decimals with 2 digits after the decimal. If a person has 0 in both columns CGPA UG and GR it is pulling a 0 over into the new column.
Not certain if this would have any impact but since its a registration report for the students each row represents a registration so if a student has 4 registrations for that academic year then they would have 4 rows showing the same information just different information in the course column.
A slicer of [Academic Year] does change the number. But how do I make sure that I am getting an average of distinct student CGPA. Even though their name appears multiple times the CGPA value stays the same since its a year end CGPA.
Try changing your measure to
Avg CGPA = AVERAGEX( VALUES(TableName[Student ID]), AVERAGE(TableName[CGPAMerge]) )
I still don't understand what's causing it to behave that way though. That slicer should work fine with that measure even if there's no year selected. Look, here's your sample data in a quick file I put together using the regular average formula. https://drive.google.com/open?id=0B9BEw2M_e_jvT2I0TWxKRTZpdjA
I have a card with the Avg CGPA measure and an Academic Plan slicer. They interact exactly as expected. Can you look at this and tell me what's different between my file and yours?
I am going through line-by-line and comparing to your setup. Hoping that I would find something different to put an end to this nightmare but so far I have found nothing different. The only thing is I have 270,000 lines but the format is the same as the sample file I gave you just with some students appearing 3 or 4 times per academic year.
Ok so I just deleted all the slicers and recreated them and now its work. :smileyindifferent: