Convert DAX to M Code

[KaraWilson]

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"

[BA_Pete]

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]

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]

Hi @KaraWilson ,

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

Pete

[KaraWilson]

Hi @BAPete

Thank you I am now having the following issue, I have ensured that my date column is Date (have also tried Date/Time) and my custom column is Text. but that unfortunately didn't help.

Kara

[BA_Pete]

Hi @KaraWilson ,

It looks like you are trying to compare your generated date to your [InterviewResult] field, rather than to your [InterviewDate] field.

My guess is that it's here in your code:

Make sure that [InterviewDate] field is definitely typed correctly and that it definitely refers to a field with a date in it.

If this doesn't work, then please post your M calculation again exactly as it looks now and I'll recheck.

Pete

[KaraWilson]

Hi @BA_Pete 

Thank you so much that worked, I have posted my code as I am not getting the "Future Booked" result for greater than today but everything else is working perfectly now.

Kara

[BA_Pete · Accepted solution]

@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

[KaraWilson]

Thanks @BA_Pete  ughh so new to this, you're right I had in one of the hundreds of times I tried to rewrite it changed the first < to a >!

After years of using excel and very very new to Power BI, managing DAX coding quite well M coding absolutely threw me!

Your M code worked a treat and I can now investigate where I was going wrong for my own understanding.

Many thanks for your continued support. Kara

[BA_Pete]

No worries Kara, happy to help.

Best of luck with your future M learning! 🙂

Pete

[Jakinta]

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

[KaraWilson]

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]

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)