- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- SQRT error on PowerBI/PowerPivot when using column...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

will1329

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2017
08:24 PM

Hi,

I am attempting to do a workaround for the lack of NORMSINV function in PowerBI/PowerPivot. Part of my formula is:

B = 1.96*SQRT(1.96^2+4*[NUMERATOR]*(1-[NUMERATOR]/[DENOMINATOR]))

When NUMERATOR = 1 and DENOMINATOR = 1, I should get a result of 3.84.

However, in PowerBI/PowerPivot this result in the error:

An argument of function 'SQRT' has the wrong data type or the result is too large or too small.

The error is not present when using the exact same formula in excel and also goes away when I replace the column references with values (i.e. if I replace both NUMERATOR and DENOMINATOR with 1 I get the desired result).

Does anyone know why this might be happening?

Many thanks,

Will

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Greg_Deckler

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2017
09:04 PM

The documentation on SQRT states that it returns an error for negative numbers. You are most likely getting negative numbers passed to the SQRT function. To test, try creating this column:

D = VAR myvalue = [NUMERATOR]/[DENOMINATOR] VAR myvalue2 = 1.96^2 VAR myvalue3 = myvalue2+4*[NUMERATOR]*(1-myvalue) RETURN myvalue3 // RETURN 1.96*SQRT(myvalue3)

If you see negative numbers, you have your answer. See documentation here:

https://msdn.microsoft.com/en-us/library/ee634559.aspx

Note the Remarks section.

You could potentially fix this by wrapping the inside of the SQRT with an ABS. You could create a VAR to see if the value that you are taking the SQRT of is negative and, if so, multiply by -1 outside of the SQRT if you really need the negatives.

Proud to be a Datanaut!

Dave1

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2017
09:19 PM

Hi,

The issue is caused by negative results for SQRT function.

Create a measure for your numerator = sum(YourNumerator)

Create a measure for your denominator = sum(YourDenominator)

Test logic

Measure = Measure = 1.96*SQRT(1.96^2+4*1*(1-1/1)) === 3.84

Prod Code

Measure2 = Calculate(1.96*SQRT(ABS(1.96^2+4*[sum1]*(1-[sum1]/[Sum2]))))

Measure and Measure 2 produce the same result for Field A, where Numerator and denominator are 1.

3 REPLIES 3

Greg_Deckler

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2017
09:04 PM

The documentation on SQRT states that it returns an error for negative numbers. You are most likely getting negative numbers passed to the SQRT function. To test, try creating this column:

D = VAR myvalue = [NUMERATOR]/[DENOMINATOR] VAR myvalue2 = 1.96^2 VAR myvalue3 = myvalue2+4*[NUMERATOR]*(1-myvalue) RETURN myvalue3 // RETURN 1.96*SQRT(myvalue3)

If you see negative numbers, you have your answer. See documentation here:

https://msdn.microsoft.com/en-us/library/ee634559.aspx

Note the Remarks section.

You could potentially fix this by wrapping the inside of the SQRT with an ABS. You could create a VAR to see if the value that you are taking the SQRT of is negative and, if so, multiply by -1 outside of the SQRT if you really need the negatives.

Proud to be a Datanaut!

Dave1

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2017
09:19 PM

Hi,

The issue is caused by negative results for SQRT function.

Create a measure for your numerator = sum(YourNumerator)

Create a measure for your denominator = sum(YourDenominator)

Test logic

Measure = Measure = 1.96*SQRT(1.96^2+4*1*(1-1/1)) === 3.84

Prod Code

Measure2 = Calculate(1.96*SQRT(ABS(1.96^2+4*[sum1]*(1-[sum1]/[Sum2]))))

Measure and Measure 2 produce the same result for Field A, where Numerator and denominator are 1.

will1329

Frequent Visitor

Re: SQRT error on PowerBI/PowerPivot when using column references

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2017
10:51 PM

Hi,

Thanks for the help both. Thought I'd made sure there were no negatives but a few had slipped past as the records contained blanks (12 out of 15million isn't too bad I think!). I've added your useful suggestions to deal with negatives for extra certainty. Just goes to show you should check your data before you analyse it

Thanks