Forum Discussion
Problem With Decimals in Calculated Column with If >=
- 6 years ago
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.
Anonymous
Apologies, if it wasn't clear before.
So, when I try ">= 0.745" and prese enter, it is automatically converted to ">=0,745".
So Column = IF('Table'[Column] >= 0.745; "Yes") is automatically converted to Column = IF('Table'[Column] >= 0,745; "Yes").
I have also already tried with and without BLANK. (I will need the BLANK later, when I embed this into the larger function.)
The column works with ">=1", and all columns, imported or calculated, display in European format. But apparently the decimals are not being recognized in the functions. So it has to be a mismatch in regional settings somewhere... right?
Any other ideas?
'Table'[Column] >= 745/1000
This IS independent of any settings. Set your computer's regional settings correctly and it should be OK.
Best
D
- MJEnnis6 years ago
Resolver III
Yes. Same result with 745/1000 as 75/100. I also tried 7450/10000. All produce the correct decimal in European format.