Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JMWDBA
Advocate II
Advocate II

CASE Statement to DAX

So I have the below:

 

CASE

     WHEN [Academic Career] = Undergraduate THEN [CGPA UG]

     WHEN [Academic Career] = Masters THEN [CGPA GR]

     ELSE 'No CGPA'

END

 

How do I change this into DAX for Power BI?  Is such an approach possible in DAX?  [Academic Career], [CGPA UG], and [CGPA GR] are columns. I want to create a new column that grabs the value from each of those two columns based on the case statement. 

 

3 ACCEPTED SOLUTIONS
KHorseman
Community Champion
Community Champion

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"
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

KHorseman
Community Champion
Community Champion

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

39 REPLIES 39
Vvelarde
Community Champion
Community Champion

@JMWDBA

 

Hi. Use SWITCH Function

 

MeasureorColumn=Switch(Table[Academic Career],"Undergraduate",Table[CGPA UG];"Masters",Table[CGPA GR],Table[NO CGPA])




Lima - Peru
JoeSQL
Frequent Visitor

Nested IF statements will do the trick:

 

=IF (
    [Academic Career] = "Undergraduate",
    [CGPA UG],
    IF ( [Academic Career] = "Masters", [CGPA GR], "No CGPA" )
)

 

 

Hi @JoeSQL,

 

I tried that and got the message "Expressions that yield variant data-type cannot be used to define calculated columns."

Vvelarde
Community Champion
Community Champion

@JMWDBA

 

This error is because you are mixing data types. I Suspect Numbers and Texts.




Lima - Peru
JoeSQL
Frequent Visitor

I'm guessing [CGPA UG] and/or [CGPA GR] contain numbers. You're getting that error because No CGPA is a text data type. Try substituting "No CGPA" with 0

KHorseman
Community Champion
Community Champion

What data type is the [CGPA GR] column?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It is Decimal Numbers. In some cases the values present as "null" instead of just showing "0".  Zero means they do not have a CGPA at that level. 

 

2017-05-31_16-20-37.jpg

 

KHorseman
Community Champion
Community Champion

Use the FORMAT function to change the numbers to strings. Something like...

 

MeasureorColumn= SWITCH(
	Table[Academic Career],
	"Undergraduate",FORMAT(Table[CGPA UG], "General Number",
	"Masters", FORMAT(Table[CGPA GR], "General Number",
	"No CGPA"
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

 

This is what I got:

 

2017-05-31_16-35-00.jpg

KHorseman
Community Champion
Community Champion

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"
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman 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. 

KHorseman
Community Champion
Community Champion

I'm not sure I understand what you're asking. What is the purpose of this other column?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @KHorseman,

 

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. 

KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you @KHorseman.  That worked perfectly.  I would never have got the SWITCH thing.  totally a new language for me. 

KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman 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. 

 

2017-06-01_9-50-21.jpg

KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I checked and the interaction is turned on. 

KHorseman
Community Champion
Community Champion

And no matter what you select in that slicer, it still says the average is 3.30?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.