DAX IF() no longer working

[TBardien]

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:

Spoiler

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:

Spoiler

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

[Anonymous · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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

[TBardien]

@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???

[Anonymous]

One thing, though... By no means is if( x,, blank(), x) correct syntax in any decent language. As much as this if(x.. blank(). x) wouldn't be. Of course you can express 0 as 0. (if you have "." as the decimal point) but then you wouldn't use "." as the separator. This is exactly the same with ",". In German, the separator, as much as I remumber, is ";", not "." and not ",".

Best
D

[TBardien]

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)

[Anonymous]

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

[TBardien]

@Anonymous 

Changing the language settings does not help.

[Anonymous]

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 😞

[Anonymous]

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

Do the above.

Best

D

[TBardien]

@Anonymous 

nope, still not working:

[Anonymous]

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

[TBardien]

@Anonymous 

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

[Anonymous]

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

[TBardien]

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

[Anonymous]

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

[nandukrishnavs]

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

[TBardien]

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

[nandukrishnavs]

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

[TBardien]

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