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.
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.
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:
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)
Many thanks,
A
Solved! Go to 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
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 error
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |