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 hav...
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

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
Icon for Super User rankSuper User
4 years ago

Hi KaraWilson ,

 

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

 

Pete

  • KaraWilson's avatar
    KaraWilson
    Frequent Visitor
    4 years ago

    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's avatar
      BA_Pete
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        KaraWilson
        Frequent Visitor
        4 years ago

        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