Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
MJEnnis
Helper V
Helper V

Problem With Decimals in Calculated Column with If >=

I am testing the following basic function before I embed it in a much more complicated function with multiple IF statements, etc.: Column = IF('Table'[Column] >= 0,745; "Yes"; BLANK()) Many of the values in the source column should result "Yes". But all cells in the calculated column return blank. The source column data type is decimal number but is formatted as a percentage. The data is from a Central European source and is formatted accordingly (commas in place of decimal points). Location is set to the proper Central European country (although my visuals still display decimal points for decimals instead of commas, which is what I prefer). When I replace the function with ">= 1", then it works properly. Any ideas why it is not recognizing decimals? Thanks!

1 ACCEPTED SOLUTION

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.

 

View solution in original post

19 REPLIES 19

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors