Forum Discussion
Resolver IIIProblem With Decimals in Calculated Column with If >=
Solved! (Though I may have created further headaches later on.)
So I think the problem was that the region settings on my previous laptop were different. I imported the data from an Oracle database on the previous machine. And I started cleaning and structuring the data and creating the report on that machine. That machine crashed, and I do not remember the region settings.
The new machine has been running with Windows set to English (US). I downloaded Power BI Desktop to my new machine and opened the pbix file wthout taking regions into consideration. Everything worked fine for several weeks until I included a decimal in a function.
To solve the problem, I have been messing around with various combinations of region settings across Windows and PowerBi.
This is the combination that finally worked:
1) Dates and numbers are formatted for Central Europe in the database.
2) The file is set to Italian (Italy) in PowerBI.
3) The global application language is set to Windows default..
4) The global model language is set to English (US).
5) Windows is set to Engish (US).
6) But I have customized the number format for Windows to match European format.
I do not know why, but setting the region for Windows to Italian (Italy) did not do the trick. I had to keep it set to English (US) and then customize the number format for Europe.
Oh, and here is the real kicker. After doing all of this, I have to type "0.745" in the function. But thee column results for decimals appear with the European commas. And in my visuals, it is back to US formatting.
I am satisfied with this result. But I am very scared to open some of my Excel files and find out how they are responding to the new number format. Perhaps I will have to keep switching it depending on which application/files I open.
Community ChampionIt's very strange. I would report this to Microsoft and have then take a look at it. There is an issues forum here:
https://aka.ms/PBI_Comm_Issues
And if it is not there, then you could post it.
If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".
Again, I'd like to help more but the regional settings stuff is just too difficult to troubleshoot. Seems like there is something definitely confusing Power BI though, some combination of OS regional settings, Power BI regional settings, etc. What are the regional settings for you OS and for Power BI?
Resolver IIIThanks a lot! I will wait and see if anyone else replies here and then try the Issues page and/or sending a ticket.
Try to create a calculated column and put some constant in there (change the constant several times to see what's going on) and then check the expression on that column.
You could also create a small calculated table and try things out on it. Don't be lazy 🙂
Best
D- MJEnnis
Anonymous
So, if I create a calculated column in the same table as:
column = divide(3;4)
The result is automatically recognized as a decimal number and correctly formatted for Europe as 0,75.
However, if I put in the following
column = 0,75
The result is still decimal number, but formatted as general and appears 75. When I format to appear as decimal number, I get 75,00.
Replacing with
column = 0.75
produces the same result, only that PowerBI automatically edits my formula to 0,75.
Obviously something up with regional formatting, but no clue what.
Then... you maybe should try this:
// You don't need BLANK as the second argument. // It's the default when none is supplied. Column = IF('Table'[Column] >= 0.745; "Yes")Best
D