topic Re: Problem using IF with new column (Syntax error) in DAX Commands and Tips

Problem using IF with new column (Syntax error)

JayB — Sun, 15 Mar 2020 19:35:25 GMT

Hello,

I'm following this guide ( https://insightsoftware.com/blog/working-with-weeks-in-power-bi/ ) to get week numbers, day numbers and names for my dataset. I'm able to add 2 new columns for the week/day numbers just like in the guide.
I'm failing at the step to add the day names column.

I'm adding the column with the following code:

Day Name = IF('123it_otrs time_accounting'[Week Day]=1,"Monday", IF('123it_otrs time_accounting'[Week Day]=2, "Tuesday", IF('123it_otrs time_accounting'[Week Day]=3, "Wednesday", IF('123it_otrs time_accounting'[Week Day]=4, "Thursday", IF('123it_otrs time_accounting'[Week Day]=5, "Friday", IF('123it_otrs time_accounting'[Week Day]=6, "Saturday", "Sunday"))))))

and I'm getting the following error:

The syntax for '"Monday"' is incorrect. (DAX(IF('123it_otrs time_accounting'[Week Day]=1."Monday",IF('123it_otrs time_accounting'[Week Day]=2. "Tuesday",IF('123it_otrs time_accounting'[Week Day]=3. "Wednesday",IF('123it_otrs time_accounting'[Week Day]=4. "Thursday",IF('123it_otrs time_accounting'[Week Day]=5. "Friday",IF('123it_otrs time_accounting'[Week Day]=6. "Saturday", "Sunday")))))))).

I'm not sure wher I'm going wrong and I'm starting to think the issue is that I have to use ' ' to access my columns?

Re: Problem using IF with new column (Syntax error)

Greg_Deckler — Sun, 15 Mar 2020 19:36:19 GMT

I'll take a closer look in a minute, but in general, avoid nested IF statements and use SWITCH instead. Way cleaner.

Re: Problem using IF with new column (Syntax error)

JayB — Sun, 15 Mar 2020 19:42:47 GMT

Good comment, (I agree), but I'm getting the exact same error:

=SWITCH('123it_otrs time_accounting'[Week Day], 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saterday", 7, "Sunday")

Unexpected expression starting at "Monday".

I'm very new to this and still following guides, so I may be failing to grasp the basic concepts here.

Re: Problem using IF with new column (Syntax error)

Greg_Deckler — Sun, 15 Mar 2020 19:50:03 GMT

Yeah, that's bizarre because I copied and pasted your code and it worked perfectly for me. Perhaps something wrong with quotes or double quotes?

Both of these worked, PBIX is attached.

This is a column, right?

Re: Problem using IF with new column (Syntax error)

JayB — Sun, 15 Mar 2020 19:58:57 GMT

Thank you for attaching the example file.

When I open the file, it's good, when I select the column, I see the expression, it gives me an error and it breaks.:

Re: Problem using IF with new column (Syntax error)

Greg_Deckler — Sun, 15 Mar 2020 20:01:49 GMT

What is the language version of your Desktop?

Re: Problem using IF with new column (Syntax error)

JayB — Sun, 15 Mar 2020 20:06:18 GMT

I'm on a Windows 10 Pro, The display language is set to English (United States), The preferred languages are "English (Belgium)"/"English (United Kingdom)".

I did not realise this might work like Office, where formulas follow the language. But it should be english.

Re: Problem using IF with new column (Syntax error)

JayB — Sun, 15 Mar 2020 20:12:36 GMT

In the powerBI options, regional settings, I forced everything to English (United states), but still the same issue.

Re: Problem using IF with new column (Syntax error)

Greg_Deckler — Sun, 15 Mar 2020 20:15:54 GMT

Wait, try semi-colons instead of commas. JayB

Re: Problem using IF with new column (Syntax error)

JayB — Sun, 15 Mar 2020 20:25:25 GMT

Googling I also found a post where they suggested to use ;, is this what you mean?

The "intellisense?" example does show me to use ,

Using integers instaid of strings does work. So it's something about the strings.

Re: Problem using IF with new column (Syntax error)

Greg_Deckler — Sun, 15 Mar 2020 20:30:59 GMT

OK, try single quotes??

Re: Problem using IF with new column (Syntax error)

JayB — Sun, 15 Mar 2020 20:39:28 GMT

Right, sry, I didn't bother to post that, but I have tried that. Same issue. 😕

Re: Problem using IF with new column (Syntax error)

Greg_Deckler — Sun, 15 Mar 2020 20:41:58 GMT

OK, this is highly strange. File | Options and settings | Options | Diagnostics, What version of the Desktop?

Also, under Regional Settings what are those:

Re: Problem using IF with new column (Syntax error)

JayB — Sun, 15 Mar 2020 21:12:43 GMT

I'm on version 2.79.5768.721 64-bit (March 2020).

I ran PowerBI on my local machine, and your example file ran just fine. (I'm using a VM and RDP to it).
In the VM I've set all regional settings to the United states, I've manually set all powerbi regional settings to united states (This where on that already) and rebooted. And now it works.

To conclude. I want to apologise for wasting your time, this was not a DAX question, but "how do I use powerbi". (I sure wasted a lot of time this week trying to gras why even the most basic things failed for me). I didn't realise this worked like Excel, where if you change the language, formulas change.

Thank you for your help!

Re: Problem using IF with new column (Syntax error)

Greg_Deckler — Sun, 15 Mar 2020 21:15:30 GMT

No worries JayB I've just been working on a blog post and answering forum questions most of the day. Nothing to do with everything shut down over COVID-19!!