Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to Solution.
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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.
Hi @KaraWilson ,
You didn't change your DateTime.LocalNow()-1 code to Date.AddDays(DateTime.LocalNow(), -1)
Pete
Proud to be a Datanaut!
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
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
Proud to be a Datanaut!
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
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
Proud to be a Datanaut!
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
No worries Kara, happy to help.
Best of luck with your future M learning! 🙂
Pete
Proud to be a Datanaut!
Since M-code is case sensitive, did you try with null intead of Null and "if " (with space) instead of "if"?
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
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |