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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adavid999
Helper V
Helper V

SQRT problem in DAX/excel - too many decimal places?

Hi I am having trouble with sqrt in a dax measure in Excel and would be grateful for assistance.

 

When I try to place SQRT meaure in pivot table I receive following message.

 

Capture.PNG

 

The data I am working on all looks fine (and I have recreated calcs manually and they are ok) - though i was wondering if decimal places are causing the issue and if so how do I restrict these. An example of the Standard Error result I want the SQRT of looks like:

Capture2.PNG

The actual number is correct - though maybe too big? How do I reduce? or could something else be responsible? I have tried editing the measure settings and although I set 5 decimal places, the underlying number does not change (see formula bar below)

Capture3.PNG

Many thanks,

 

A

 

1 ACCEPTED SOLUTION

Hi @adavid999,

 

From the measure and error you provided I would actually suspect your issue isn't the rounding of the measure itself but the fact the result of the measure is too small ie. 0.000000000000000000000000000000000000000 (and more thousands of zeros) 0001 or too large ie. infinity. In technical terms this is called numerical underflow/overflow

 

Have you checked to make sure that in your measure the numerator or denominator doesn't evaluate in such a way that one is very small and one is very large?

 

Kris

View solution in original post

7 REPLIES 7
jdbuchanan71
Super User
Super User

@adavid999 

Maybe try putting a ROUND () on the measure and setting it to 10 or 15 digits?

thanks @jdbuchanan71 that didn't work. I get a similar errorCapture.PNG

Do you know if there is a way of setting the decimal places when creating the measure?

 

I am assuming that this is the problem btw - I can't think of anything else that might be causing it. I will restart my computer just in case!

A

Hi @adavid999,

 

From the measure and error you provided I would actually suspect your issue isn't the rounding of the measure itself but the fact the result of the measure is too small ie. 0.000000000000000000000000000000000000000 (and more thousands of zeros) 0001 or too large ie. infinity. In technical terms this is called numerical underflow/overflow

 

Have you checked to make sure that in your measure the numerator or denominator doesn't evaluate in such a way that one is very small and one is very large?

 

Kris

Just for info - this came up again and I think your response was the answer @kris .

I wasn't using the safe DIVIDE measure (I didn't know it existed), all my measures to calculate standard error and SQRT were using "/". Switching to DIVIDE function seems to have solved the problem.

Cheers,

A

Hi @adavid999,

 

Great news! Yes I'm always cautious when calculating variances as it only takes one calculation to have a very small (or zero) denominator and the number it returns can be huge or even infinity. If you suspect this is ever going to be the case the DIVIDE function is great 🙂! Only note is that there could be a slight performance hit as there's some extra work for the engine to do to resolve these math errors or under/overflows, but this is only noticable when datasets get huge. Links below

 

 

Kris

Thanks @kriscoupe I don't think it's that but playing around I think it might be whenever I load any new measure to this partcular pivot table it's loading as a % and then I have reformat on the measure value settings to ensure it is a number. So the default is % - any idea how to change that?

 

I'm sure it's something simple - it's not something I can do from within the 'edit measure dialogue box'. It doesn't seem to be something I can change from within the pivot table field settings or options, either.

 

Update

I have started the pivot table again and the problem disappears so something to do with pivot table auto-formatting new measures to %!

Interesting, yes maybe something was "stuck" (for lack of a better term) on that specific Pivot Table. The turn it off and back on again approach works wonder sometimes hey 😀.

 

And not sure why it would auto format to percentage I'm afraid but it's good to hear everything is sorted now!

 

Kris

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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