topic DAX IF() no longer working in DAX Commands and Tips

DAX IF() no longer working

TBardien — Fri, 17 Apr 2020 09:57:24 GMT

I am new to Power BI.

I am taking reports over from a previous employee in my company. I downloaded the .pbix file from app.powerbi.com for updating.

When I opened up a custom column to edit it I get errors in the IF() statement, though the report seems to be running and refreshing. The errors are "unexpected expression 'BLANK'" and "unexpected expression '_time'".

The code looks like this:

Time in Quoting (hours) =

var _time = DATEDIFF(RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE) / 60

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

However, when I click enter on the code (without changing any of the code) the report breaks and I get a DAX syntax error:

The syntax for 'BLANK' is incorrect. (DAX(var _time = DATEDIFF(RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE) / 60return IF(_time < 0. BLANK(), _time))).

Can anyone point me in the right direction as to why this is happenning?

Thank You

Re: DAX IF() no longer working

nandukrishnavs — Fri, 17 Apr 2020 10:02:39 GMT

TBardien Looks like you are using "." instead of a comma.

Corrected

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

If you find this answer useful, please mark this as an accepted solution.

Re: DAX IF() no longer working

TBardien — Fri, 17 Apr 2020 10:16:59 GMT

nandukrishnavs I am using a "," You can see when you view the code, However, when the error pops up, it shows a "." This is the code that breaks:

Time in Quoting (hours) =

var _time = DATEDIFF(RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE) / 60

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

Re: DAX IF() no longer working

nandukrishnavs — Fri, 17 Apr 2020 10:21:24 GMT

TBardien - I have formatted the DAX measure.

Try to copy-paste below code.

Time in Quoting (hours) = VAR _time = DATEDIFF ( RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE ) / 60 RETURN IF ( _time < 0, BLANK (), _time )

Re: DAX IF() no longer working

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

Copied the code, still the same error:

The syntax for 'BLANK' is incorrect. (DAX(VAR _time = DATEDIFF ( RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE ) / 60RETURN IF ( _time < 0. BLANK (), _time ))).

Re: DAX IF() no longer working

TBardien — Fri, 17 Apr 2020 10:45:54 GMT

nandukrishnavs

Re: DAX IF() no longer working

Anonymous — Fri, 17 Apr 2020 11:11:29 GMT

Time in Quoting (hours) = VAR _time = DATEDIFF ( RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE ) / 60 RETURN IF ( _time >= 0, _time )

Do the above.

Best

Re: DAX IF() no longer working

TBardien — Fri, 17 Apr 2020 11:21:10 GMT

Anonymous

nope, still not working:

Re: DAX IF() no longer working

Anonymous — Fri, 17 Apr 2020 11:25:30 GMT

The DAX is correct. It's something wrong with your file. Please update PBI Desktop to the latest version and refresh the model.

By the way, is [Time in Quoting (hours] a measure or a calc column? If it's a measure, then this DAX might or might not be correct. I can't say because you're not following Best Practices. Are '...'[Quoted] and '...'[Quoting] measures?

Best
D

Re: DAX IF() no longer working

TBardien — Fri, 17 Apr 2020 11:28:14 GMT

Anonymous

calc column i think, this is the properties of it.

Re: DAX IF() no longer working

Anonymous — Fri, 17 Apr 2020 11:31:21 GMT

OK. Try to remove the IF and instead return just _time. See if this works. If not, change the name of the variable to __time (with 2 underscores).

Tell me the outcome.

Best
D

Re: DAX IF() no longer working

TBardien — Fri, 17 Apr 2020 12:27:58 GMT

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

Re: DAX IF() no longer working

Anonymous — Fri, 17 Apr 2020 12:32:23 GMT

This is very weird. The above can be written as:

( [ProductR Top N] < 4 ) * [Returns]

Your previous one can be written as:

( _time >= 0 ) * _time

But not being able to use IF... that's not good 😞

Best
D

Re: DAX IF() no longer working

Anonymous — Fri, 17 Apr 2020 12:44:44 GMT

Try a second "," in your code

Time in Quoting (hours) =

var _time = DATEDIFF(RequestWorkflowHistory[Quoting], RequestWorkflowHistory[Quoted], MINUTE) / 60

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

Power BI expect a number after your first , like 0,1 and waits for the second statement of if.

Hope it's working for you

Bruening

Re: DAX IF() no longer working

Anonymous — Fri, 17 Apr 2020 12:48:53 GMT

Hi Anonymous.

If you had read the whole conversation, you'd have known that IF( _time >= 0, _time ) does not work, either. So it's not BLANK that's the issue.

And of course, if your code works, I'll have to eat my hat 🙂

Best
D

Re: DAX IF() no longer working

Anonymous — Fri, 17 Apr 2020 13:04:50 GMT

Hi Anonymous.

I hope for you, that my code dosen't work. I don't like eating hats 😉

Yes your right, BLANK isn't thr problem. Its the number. I expect that Power BI try to check "_time < 0.BLANK()" but can't handle the number 0.BLANK (or" 4.Returns" in the other case). Probably he has german numbers in Porwer BI. We use , to seperate between integer and decimal.

Best regard

Bruening

Re: DAX IF() no longer working

TBardien — Fri, 17 Apr 2020 13:15:34 GMT

Anonymous Anonymous

It seems to be working. This is crazy:

When I change the returniftrue parameter to an integer it works, So I did suspect PowerBI couldn't handle a variable for that parameter. But as I said, the sample code also did not work, which through me off.

How do I go about changing my PowerBI not to use german numbers???

Re: DAX IF() no longer working

Anonymous — Fri, 17 Apr 2020 13:29:09 GMT

Hey TBardien ,

the sample code is not working with the same problem. "4.Return" is not a correct number for Power B.

I am not sure if its enoght to change the language in File --> Options & sttings --> options --> Regional settings

but ou can try a shot there 🙂

Best Regards

Bruening

Re: DAX IF() no longer working

TBardien — Fri, 17 Apr 2020 13:56:25 GMT

Anonymous

Changing the language settings does not help.

Re: DAX IF() no longer working

Anonymous — Fri, 17 Apr 2020 14:04:38 GMT

Did you try it with an new report or with you old report. The model language can't be changed. If it's not working i haven't an idea what to do 😞