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"
When [Planned Contract Award Date]<[Current Date] then "Past Due"
when [Execution Status]="Not Started" then If([Current Date]<[ITB/RFP Prep Start Date (P50)],"Projected","Off Track")
when [Execution Status]="RFP Prep" then If([Current Date]<[ITB/RFP Release Date (P50)],"On Track","Off Track")
when [Execution Status]="RFP Released" then If([Current Date]<[Award Recommendation],"On Track","Off Track")
when [Execution Status]="Contracting" then If([Current Date]<[Planned Contract Award Date],"On Track","Off Track")
end

 

Thank you!

Need Help

3 Replies

  • 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