Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Hi @erceve ,
When I modified DOT to COMMA in the pbix that I shared, the formula works.
I am sharing the updated pbix here.
If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂
Thanks,
Pragati
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
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
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
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......
Hi @erceve ,
If this has helped you in resolving your issue can you please mark my suggestion as a solution? 🙂
Thanks,
Pragati
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
wow that is a quick reply, here is a screenshot from machine 2, as you can see everything appears to have been replaced correctly
Here's a link for the pbix file, thx in advance!
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
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
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!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |