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?
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:
:smileyfrustrated: