Forum Discussion

JMWDBA's avatar
JMWDBA
Advocate II
9 years ago
Solved

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. 

 

  • Anonymous's avatar
    Anonymous
    9 years ago

    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"
    )
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

  • Anonymous's avatar
    Anonymous
    9 years ago

    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?

39 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion

    JMWDBA

     

    Hi. Use SWITCH Function

     

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

  • JoeSQL's avatar
    JoeSQL
    Frequent Visitor

    Nested IF statements will do the trick:

     

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

     

     

    • JMWDBA's avatar
      JMWDBA
      Advocate II

      Hi JoeSQL,

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable

        What data type is the [CGPA GR] column?