Forum Discussion
Convert DAX to M Code
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
Super UserHi 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
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
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