Problem With Decimals in Calculated Column with If >=

[MJEnnis]

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!

[Anonymous]

In PBI Desktop go to File > Options and Settings > Options > Regional Settings in both the Global section and Current File. Make changes accordingly.

Best
D

[MJEnnis]

So I realized that the model was set to English (US) and the file was set to Italian (Italy). 

I have changed the model to Italian (Italy), saved, closed, and reopened. But nothing changes. In fact, it says you cannot change the global setting for the model after you have created the report. 

Perhaps when I got a new work computer and installed PowerBI, it got confused. The current machine is set to English (US). Maybe the old one was set to Italian...

[MJEnnis · 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.

[Anonymous]

You can create a batch/PowerShell file that will switch between the regional settings of your Windows, so you don't have to do it manually.

Best
D

[Greg_Deckler]

Hmm, interesting... You know, I'd never thought about it quite like that before. This has changed my entire perspective on things. I appreciate you going into such a lengthy description and explanation of your experiences.

[MJEnnis]

@Greg_Deckler I will be sure to accept your reply as the solution once I figure this out.

[Greg_Deckler]

So what happens if you use this:

Column = IF('Table'[Column] >= 0.745; "Yes"; BLANK()) 

?

Also, are you positive that this is a numeric column (right justified and italic) Probably if 1 works. Going to be a difficult one for me to recreate with the different regional settings going on.

[MJEnnis]

I've tried ">=0.745" and it simply replaces the period with comma once I hit enter. And the data type is definitely set as decimal number. Perhaps I should also mention that it is a calculated table with 9 other columns, all calculated off data in this table or others in the model. But all other formulas work properly (some producing decimals). This is the only one that has a decimal in the function, though.

The sourcel column is also a calculated column, with decimals, obviously. But it works when I use ">=1".

[Greg_Deckler]

It'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?

[MJEnnis]

Thanks a lot! I will wait and see if anyone else replies here and then try the Issues page and/or sending a ticket.

[Anonymous]

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.

[Anonymous]

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

[MJEnnis]

@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?

[Anonymous]

Does the column work with this?

'Table'[Column] >= 745/1000

This IS independent of any settings. Set your computer's regional settings correctly and it should be OK.

Best
D

[MJEnnis]

Yes. Same result with 745/1000 as 75/100. I also tried 7450/10000. All produce the correct decimal in European format. 

[Anonymous]

You could also try

'Table'[Column] >= 745/1000

If the above does not work, then... there's something seriously wrong with your instance of PBI Desktop or the computer.

Best
D

[Greg_Deckler]

Sorry, was being sarcastic because all I could see was the title of your post and nothing in the actual post itself. I'll read through it now that I see something! 🙂

[MJEnnis]

@Greg_DecklerYeah, it was my first post and I clicked "post" before I wrote it! Thanks for taking a look!