Forum Discussion

KaraWilson's avatar
KaraWilson
Frequent Visitor
4 years ago
Solved

Convert DAX to M Code

Hi All

 

I am having some trouble converting DAX to M Code, I have data that I need to change the results on but then need to create a conditional column so am unable to use as a DAX column - I have tried to reproduce as a custom column but very new to Power BI and unable to get my M code to work.

 

Any assistance would be much appreciated. thanks.

 

DAX coding:

IF (ISBLANK ( [InterviewDate]),"",

IF (AND (ISBLANK ( [InterviewResult]),[InterviewDate]<= TODAY () - 1),"No Result",

IF (AND (ISBLANK ( [InterviewResult]),[InterviewDate] >= TODAY ()),"Future Booked",

IF ([InterviewResult] = "Attended","No Result",

IF([InterviewResult]=”Failed to Attend,”FTA”,[InterviewResult]))))

 

Failing M Code:

if[InterviewDate]= Null

then Null

else

if[InterviewResult]= null and [InterviewDate]<=DateTime.LocalNow-1

then "No Result"

else

if[InterviewDate]>=DateTime.LocalNow

then "Future Booked”

else

if[InterviewResult]="Pass"

then "Pass"

if[InterviewResult]="Conditional Pass"

then "Conditional Pass"

if[InterviewResult]="Failed to Attend"

then "FTA"

else "No Result"

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    KaraWilson ,

     

    You're not getting your 'Future Booked' outputs because your first condition is negating it.

     

    Your first condition reads as:

    if [interviewDate] is yesterday or later then 'No result'

    Your 'Future Booked' condition reads as:

    if [interviewDate] is today or later then "Future Booked"

     

    Can you see how your second condition has already been evaluated as part of the first condition?

     

    The calculation that you posted right at the start of this thread had the first condition as:

    if [interviewResult] is blank and [interviewDate] was yesterday or earlier then "No result". It looks like this condition has changed in the evolution.

     

    Here's your original (presumably working as required) DAX calculation, properly converted to M code (because I'm nice like that):

    let
  Date.Today = Date.From(DateTime.LocalNow())
in
if [InterviewDate] = null then null
else if [InterviewResult] = null and [InterviewDate] <= Date.AddDays(Date.Today, -1) then "No Result"
else if [InterviewResult] = null and [InterviewDate] >= Date.Today then "Future Booked"
else if [InterviewResult] = "Attended" then "No Result"
else if [InterviewResult] = "Failed to Attend" then "FTA"
else [InterviewResult]

     

    Pete

12 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    4 years ago

    Since M-code is case sensitive, did you try with null intead of Null and "if " (with space) instead of "if"?

    • KaraWilson's avatar
      KaraWilson
      Frequent Visitor
      4 years ago

      Hi Jakinta 

      No I hadn't tried that but have done so now and it is still not working, the error I am getting is 'Token Else Expected' after the row that says 

      if[InterviewResult]="Pass"

      then "Pass"

      else.....

      The first occurance of "Pass" is also underlined in red as to show that's where the error is.

       

      Thanks

      • Jakinta's avatar
        Jakinta
        Solution Sage
        4 years ago

        That means you are missing else after "Pass". 
        You have to follow nested if statements pattern.

         

        if ....

        then ...

            else if ....

            then ...

                else if ....

                then ...

                 else .... (at the end)

         

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi KaraWilson ,

     

    DateTime.LocalNow has function syntax to materialise current datetime, so try putting '()' at the end of it.

    It should look like this everywhere it features in your code:

     

    DateTime.LocalNow()

     

     

    Your DateTime.LocalNow-1 code should be rewritten ike this:

     

    Date.AddDays(DateTime.LocalNow(), -1)

     

     

    You may get unexpected results with this type of comparison syntax:

     

    DateTime.LocalNow() is a datetime output so if your [InterviewDate] field is Date only type you are not getting an apples-for-apples comparison. If this is the case, match the data types to compare by using Date.From:

     

    Date.From(DateTime.LocalNow())

Date.From(Date.AddDays(DateTime.LocalNow(), -1))

     

     

    You've also missed two else statements as far as I can see:

     

    Pete

    • KaraWilson's avatar
      KaraWilson
      Frequent Visitor
      4 years ago

      Hi Both 

      Thank you feel like I am getting somewhere - but still not fixed completely.

      I had spotted the missing else and have also added in the () to the date/time and I no longer have a syntax error but the formula is still erroring.

      New code is 

      if [InterviewDate]= null

      then null

      else if [InterviewResult]= null and [InterviewDate]<=DateTime.LocalNow()-1

      then "No Result"

      else if [InterviewDate]>=DateTime.LocalNow()

      then "Future Booked"

      else if [InterviewResult] = "Pass"

      then "Pass"

      else if [InterviewResult] = "Conditional Pass" then

      "Conditional Pass"

      else if [InterviewResult] = "Failed to Attend" then "FTA" else "No Result"

      New column:

      New error:

      I really appreciate your help on this.

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Hi KaraWilson ,

         

        You didn't change your DateTime.LocalNow()-1 code to Date.AddDays(DateTime.LocalNow(), -1)

         

        Pete