topic Re: DAX IF() not working as expected. in DAX Commands and Tips

DAX IF() not working as expected.

TBardien — Fri, 17 Apr 2020 10:13:42 GMT

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

Re: DAX IF() not working as expected.

Greg_Deckler — Fri, 17 Apr 2020 12:04:47 GMT

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

Re: DAX IF() not working as expected.

TBardien — Fri, 17 Apr 2020 12:30:56 GMT

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

This makes me think there is a bigger issue.

Re: DAX IF() not working as expected.

Greg_Deckler — Fri, 17 Apr 2020 13:11:24 GMT

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.

Re: DAX IF() not working as expected.

Anonymous — Fri, 17 Apr 2020 13:47:40 GMT

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

Re: DAX IF() not working as expected.

TBardien — Fri, 17 Apr 2020 14:22:22 GMT

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.

Re: DAX IF() not working as expected.

Greg_Deckler — Fri, 17 Apr 2020 14:51:51 GMT

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.

Re: DAX IF() not working as expected.

Greg_Deckler — Fri, 17 Apr 2020 14:53:36 GMT

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.

Re: DAX IF() not working as expected.

TBardien — Mon, 20 Apr 2020 16:26:57 GMT

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)