topic Re: Problem With Decimals in Calculated Column with If >= in DAX Commands and Tips

Problem With Decimals in Calculated Column with If >=

MJEnnis — Thu, 23 Apr 2020 22:14:54 GMT

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!

Re: Problem With Decimals in Calculated Column with If >=

Greg_Deckler — Thu, 23 Apr 2020 22:04:20 GMT

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.

Re: Problem With Decimals in Calculated Column with If >=

MJEnnis — Thu, 23 Apr 2020 22:16:09 GMT

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

Re: Problem With Decimals in Calculated Column with If >=

Greg_Deckler — Thu, 23 Apr 2020 22:33:21 GMT

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! 🙂

Re: Problem With Decimals in Calculated Column with If >=

Greg_Deckler — Thu, 23 Apr 2020 22:36:16 GMT

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.

Re: Problem With Decimals in Calculated Column with If >=

MJEnnis — Thu, 23 Apr 2020 22:36:35 GMT

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

Re: Problem With Decimals in Calculated Column with If >=

MJEnnis — Thu, 23 Apr 2020 22:45:08 GMT

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".

Re: Problem With Decimals in Calculated Column with If >=

Greg_Deckler — Fri, 24 Apr 2020 06:13:13 GMT

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?

Re: Problem With Decimals in Calculated Column with If >=

MJEnnis — Fri, 24 Apr 2020 11:09:53 GMT

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

Re: Problem With Decimals in Calculated Column with If >=

Anonymous — Fri, 24 Apr 2020 11:38:55 GMT

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

Re: Problem With Decimals in Calculated Column with If >=

MJEnnis — Fri, 24 Apr 2020 12:05:58 GMT

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.

Re: Problem With Decimals in Calculated Column with If >=

Anonymous — Fri, 24 Apr 2020 12:19:49 GMT

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

Re: Problem With Decimals in Calculated Column with If >=

Anonymous — Fri, 24 Apr 2020 12:37:33 GMT

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

Re: Problem With Decimals in Calculated Column with If >=

MJEnnis — Fri, 24 Apr 2020 12:52:10 GMT

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?

Re: Problem With Decimals in Calculated Column with If >=

Anonymous — Fri, 24 Apr 2020 13:53:24 GMT

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

Re: Problem With Decimals in Calculated Column with If >=

Anonymous — Fri, 24 Apr 2020 13:57:07 GMT

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

Re: Problem With Decimals in Calculated Column with If >=

MJEnnis — Fri, 24 Apr 2020 15:50:27 GMT

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

Re: Problem With Decimals in Calculated Column with If >=

MJEnnis — Fri, 24 Apr 2020 16:17:41 GMT

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...

Re: Problem With Decimals in Calculated Column with If >=

MJEnnis — Fri, 24 Apr 2020 22:25:55 GMT

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.

Re: Problem With Decimals in Calculated Column with If >=

Anonymous — Fri, 24 Apr 2020 22:31:42 GMT

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