Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TBardien
Helper I
Helper I

DAX IF() not working as expected.

I am new to Power BI. I am trying to use the IF(<logical_test>,<value_if_true>[, <value_if_false>]) function.

 

This is my code:

var timeFromPending = DATEDIFF(RequestWorkflowHistory[Quote Pending Max], RequestWorkflowHistory[Quoted], MINUTE) / 60
var timeFromQuoting = DATEDIFF(RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE) / 60
var QuotingTime = IF(timeFromPending<0, timeFromQuoting, timeFromPending)
return QuotingTime

 

But I get a syntax error for "timeFromQuoting", error is: "unexpected expression 'timeFromQuoting'".

 

However, when I replace the variable for the <value_if_true> parameter, with a hardcoded value, the error seems to disappear.

example if I do something like this:

 

var timeFromPending = DATEDIFF(RequestWorkflowHistory[Quote Pending Max], RequestWorkflowHistory[Quoted], MINUTE) / 60
var timeFromQuoting = DATEDIFF(RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE) / 60
var QuotingTime = IF(timeFromPending<0,0, timeFromPending)
return QuotingTime

 

Can anyone tell me why this would be happenning?

 

PS. I also think this related to my previous post: DAX-IF-no-longer-working 

 

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Yes i think it is the same solution from your other post.

 

In the first case you got the same problem that Power BI  thinks "0,timeFromQuoting" is one expression. In the second example you have timeFromPending<0,0  (international timeFromPending<0.0) and and only the true case timeFromPending (because the real true case 0 is in the inequality statement). 

 

So again try the second "," should work

var timeFromPending = DATEDIFF(RequestWorkflowHistory[Quote Pending Max], RequestWorkflowHistory[Quoted], MINUTE) / 60
var timeFromQuoting = DATEDIFF(RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE) / 60
var QuotingTime = IF(timeFromPending<0,, timeFromQuoting, timeFromPending)
return QuotingTime

 

Best regards

Bruening

View solution in original post

When I wrote my first book, I had some of these issues when formulas were checked with international settings and I found that putting spaces after my commas in my formulas tended to fix the issues. Basically what was going on from what I could tell is that DAX ends up thinking it is a decimal point because everything is numeric. but when you put a space in, DAX wises up and realizes it is not just all one decimal number.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Spaces BEFORE the comma seems to do the trick.

So instead of:

IF(_time < 0, BLANK(), _time)

you would make it:

IF(_time < 0 , BLANK(), _time)

 
 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Yes i think it is the same solution from your other post.

 

In the first case you got the same problem that Power BI  thinks "0,timeFromQuoting" is one expression. In the second example you have timeFromPending<0,0  (international timeFromPending<0.0) and and only the true case timeFromPending (because the real true case 0 is in the inequality statement). 

 

So again try the second "," should work

var timeFromPending = DATEDIFF(RequestWorkflowHistory[Quote Pending Max], RequestWorkflowHistory[Quoted], MINUTE) / 60
var timeFromQuoting = DATEDIFF(RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE) / 60
var QuotingTime = IF(timeFromPending<0,, timeFromQuoting, timeFromPending)
return QuotingTime

 

Best regards

Bruening

@Anonymous

Yes, It is the same issue. Thank You.

@Greg_DecklerWhen I add a second comma in the IF() after the logical_test, it seems to work.

Doing this: var QuotingTime = IF(timeFromPending<0,, timeFromQuoting, timeFromPending)

@Anonymousseems to think its some numer/regional setting, but we can't seem to figure out what exactly it is.

 

 

Regional settings are found in two places. File | Options and settings | Options and then there are two places for Regional Settings, default and CURRENT FILE. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

When I wrote my first book, I had some of these issues when formulas were checked with international settings and I found that putting spaces after my commas in my formulas tended to fix the issues. Basically what was going on from what I could tell is that DAX ends up thinking it is a decimal point because everything is numeric. but when you put a space in, DAX wises up and realizes it is not just all one decimal number.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Spaces BEFORE the comma seems to do the trick.

So instead of:

IF(_time < 0, BLANK(), _time)

you would make it:

IF(_time < 0 , BLANK(), _time)

 
 
Greg_Deckler
Super User
Super User

So can you post some sample data so that we can try to replicate this easily?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

 @Greg_Deckler 
It does return if I remove the IF(). However, im going to need to be able to use the IF().
I uninstalled PowerBI and reinstalled, it still did not work.
 
I also downloaded the PowerBI sample "Sales & Returns Sample v201912Sales & Returns Sample v201912" and I got the error there too for the code:
Product Returns Top 3 = IF([ProductR Top N]<4,[Returns],0)
 
The syntax for '[Returns]' is incorrect. (DAX(IF([ProductR Top N]<4.[Returns],0))).

PowerBISampleError.JPG

 

This makes me think there is a bigger issue.

There is something not correct in your first comma. The error is showing that it thinks it is a period. Also, you can tell that there is a difference in your two commas in that IF statement. Did you copy and paste this code from somewhere? Thinking that first comma is some extended character set. Try replacing that comma with a normal comma.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors