Formula style differences between different machines

[erceve]

Hello, I have a problem where formula's work on one machine but not on the other. I've made a template file which my company can use as a base fro reporting, this file contains a calendar created with a formula:

Kalender = 
  GENERATE ( 
    CALENDAR ( DATE ( 2020; 1; 1 ); today()  ); 
    VAR currentDay = [Date]
    VAR weekday = WEEKDAY( currentDay; 2 )
    VAR week = WEEKNUM( currentDay )    
    VAR day = DAY( currentDay )
    VAR month =  MONTH ( currentDay ) 
    VAR year =  YEAR ( currentDay )
  RETURN   ROW ( 
    "dag"; day; 
    "weekdagnummer"; weekday;
	"weekdag"; switch(weekday;
					1; "maandag";
					2; "dinsdag";
					3; "woensdag";
					4; "donderdag";
					5; "vrijdag";
					6; "zaterdag";
					7; "zondag");
    "week"; week;
    "maandnummer"; month;
	"maand"; switch(month;
					1; "januari";
					2; "februari";
					3; "maart";
					4; "april";
					5; "mei";
					6; "juni";
					7; "juli";
					8; "augusutus";
					9; "september";
				   10; "oktober";
				   11; "november";
				   12; "december");
    "jaar"; year )
  )

On the machine where I've created this it works perfectly, when I save the pbix and open it on my laptop (different machine)

the semicolons are replaced with comma's and the date formula doesn't work anymore, it's replaced with DATE ( 2020, 1, 1 ), the ones get underlined in red and the errror says: unexpected expression '1,'.

Same goes for the switch cases inside the formula's where the switch recognizes the values but not the results, i've tried putting the results in either double or single quotes but to no avail. Can somebody please explain me what's going on and how to make this formula work on any machine?

[erceve]

@Pragati11 

appears I was too optimistic, the formula doesn't give any errors but also does not calculate anymore so it appears I'm still stuck

[Pragati11]

Hi @erceve ,

When I modified DOT to COMMA in the pbix that I shared, the formula works.

I am sharing the updated pbix here.

https://wetransfer.com/downloads/5607964e5aa8dec38f47f971b36003e920200420100202/032e14de4be8498a4227...

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[erceve]

@Pragati11 

thanks, unfortunately the result for me is exactly the same as before, it shows comma's in the editor, the error message displays dots instead of comma's and the formula as a whole does not work for me

[Pragati11 · Accepted solution]

Hi @erceve ,

This really looks a strange issue. Can you check your separator settings on your machine under Regional settings?

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[erceve]

@Pragati11 

here's a screenshot of the settings: 

In the mean time I have reinstalled powerbi and updated to the most recent version as I got a version warning when i opened your edited file but to no avail

[erceve]

@Pragati11 

I've chaged the list separator in my regional settings to ; and my problem disappeared, not sure if it's the preferred solution but for now it seems to work for me. Thanks for pointing me in the right direction!

still.... feels like I shouldn't be forced to do this......

[Pragati11]

Hi @erceve ,

If this has helped you in resolving your issue can you please mark my suggestion as a solution? 🙂

Thanks,

Pragati

[Pragati11]

Hi @erceve ,

The first thing is that you haven't highlighted anything in RED in your DAX. Then going through your explanation, it looks like both the machines have a separator differently set-up, i.e Machine 1 has ';' (semicolon) and Machine 2 has ',' (comma).

Therefore your formula modofied itself from semicolon to comma. That looks perfect.

Try checking DAX on your MAchine 2 that every semicolon is correctly replaced by comma.

Also, it will be good to see the screesnhot of the DAXon Machine 2 here.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[erceve]

wow that is a quick reply, here is a screenshot from machine 2, as you can see everything appears to have been replaced correctly

[Pragati11]

Hi @erceve ,

Your DAX actually looks perfect to me. Can you share your .pbix file?

Thanks,

Pragati

[erceve]

Here's a link for the pbix file, thx in advance!

https://we.tl/t-H9rcsDZDWz  

edit: here's also a screenshot of part of the error message (apologies for the akward format) in the error message it appears to replace the comma's with full stops, maybe it's nothing but it's something I noticed and thought might be relevant

[Pragati11]

Hi @erceve ,

Thanks for sharing the pbix file. When I opened the pbix file, in your DAX I see "." not comma ","

Screenshot as below:

If your replace these highlighted DOT with COMMA, the DAX returns no errors. Can you please check this again?

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[erceve]

Hi @Pragati11 

That is really strange, as you can see in my screenshots it shows as a comma in my editor, also if I copy and paste it from my editor into something like notepad++, but the error message shows dots. Replacing the dots with comma's doesn't work for me since powerbi appears to interpret them as dots, even when I edit in notepad and then copy and paste into the editor. 

It appear I have found a solution for now and that is replacing all the faulty comma's with double comma's like so:

But I still feel like I shouldn't have to do this when switching between machines as it gets tedious really fast to have to manually check and or change this everytime when somebody else wants to work on a file on a different machine. ATM I don't have access to machine 1 on which this file was initially created so I'll go back and check what happens with when I open this pbix there again.

For now thanks a lot for your time!

[Pragati11]

Hello @erceve ,

If I see your screenshot, it's not a simple comma, it's double comma,

Try chnaging them to a single comma in your DAX.

If this helps and solves the problem, appreciate a Kudos and mark it as a solution! 🙂

Thank you

Pragati

[Pragati11]

Hi @erceve ,

If I see your screenshot, it's not single comma, it is double comma ,,

Try chnaging them to single comma in your DAX.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati