Forum Discussion

sakhtar's avatar
sakhtar
Frequent Visitor
7 years ago

Converting Case Statement to DAX

Can someone please help me convert the following Case Statement to DAX for Power BI?   case  when [Execution Status]="Executed" then "Executed" when [Execution Status]="Cancelled" then "Executed"...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, sakhtar 

You could just use IF Function to nesting it

 

IF([Execution Status]="Executed" , "Executed",
IF( [Execution Status]="Cancelled" , "Executed",
IF( [Planned Contract Award Date]<[Current Date] , "Past Due",
IF( [Execution Status]="Not Started" , If([Current Date]<[ITB/RFP Prep Start Date (P50)],"Projected","Off Track"),
IF( [Execution Status]="RFP Prep" , If([Current Date]<[ITB/RFP Release Date (P50)],"On Track","Off Track"),
IF( [Execution Status]="RFP Released" , If([Current Date]<[Award Recommendation],"On Track","Off Track"),
IF( [Execution Status]="Contracting" , If([Current Date]<[Planned Contract Award Date],"On Track","Off Track"))))))))
 
Best regards,
Lin
 
 

 

sakhtar's avatar
sakhtar
Frequent Visitor
7 years ago

Hi Lin!

 

Thank you so much for your quick response, I really appreciate it :)

 

Unfortunately, I'm getting the following error:

"DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

 

I'm sure its because I have some dates and some text fields but how do I keep my current formats and still do the IF statement. I need to keep my dates formatted to do other calculations so I cannot convert everything to either TEXT or DATE.

 

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, sakhtar 

    You could put steps of conversion in the formula. otherwise, how to compare a date value and text value.

    Of course, Consider using the VALUE or FORMAT function to convert one of the values.

    If you still have the problem, please share some sample data and your expected output.

     

    Best Regards,

    Lin