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
quads08
New Member

Paginated Reports DIV/0 Error

Hello everyone , I'm new to power BI report builder and have been able to google most things. However I am stuck here. 

 

I use this expression and it works 99% of the time except when Fields!ID_Last_4_Weeks_Previous_Year_ECNs_.Value is blank. 

 

Is there a way to modify this to capture that error and keep everything else intact?

 

=IIf(Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value) = 0 ,"",Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value/4)/Sum(Fields!ID_Last_4_Weeks_Previous_Year_ECNs_.Value/4))

 

 

6 REPLIES 6
v-jingzhang
Community Support
Community Support

Hi @quads08 

Have you solved this problem? If not, can you share some dummy data so that I can use it to find possible solutions? Thank you.

 

Regards,
Community Support Team _ Jing

@v-jingzhang 

No I am still stuck. How can I share the dummy data?

You can upload the file to some cloud storage platform like OneDrive or Google Drive and share the link here, so that we can download it. Remove sensitive info before sharing.

 

Additionally, I found some topics about this same error related to SSRS, maybe they can help.

https://stackoverflow.com/questions/21552118/ssrs-expression-the-value-expression-for-textbox-has-sc...

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/708b9ece-b631-4a11-90b1-f867c943040f/report...

 

Expression examples:

https://docs.microsoft.com/en-us/power-bi/paginated-reports/report-builder-expression-examples#Decis...

 

PhilipTreacy
Super User
Super User

Hi @quads08 

Always use DIVIDE() to avoid Div by 0 errors.  ANywhere you do division replace it with DIVIDE e.g.

DIVIDE( DIVIDE( Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value, 4) , DIVIDE( Sum(Fields!ID_Last_4_Weeks_Previous_Year_ECNs_.Value, 4))

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Good morning @PhilipTreacy ,

First off thanks for the quick reply! I tried to use that but it doesn't work. It gave me the following error.workingworkingnot workingnot workingPBI report builder error.JPG

 

The Value expression for the textrun 'Textbox10.Paragraphs[0].TextRuns[0]' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
----------------------------
The definition of the report '' is invalid.
----------------------------
An error occurred during local report processing.

A draw back with Power BI is that the expression will still try to calculate a value even though the IIF asks it not to.

 

I believe the answer provided by Philip is for DAX in Power BI Dashbord reports i.e. Power BI Desktop. As I understand it, in your case, you are using Power BI Report Builder (SSRS for the old folks) to build a paginated report. 

 

Power BI Report Builder has no built in Divide function. However, you can create one.

  1. With your report opened in Power BI Report Builder, right click on the dark grey area surronding your report and choose Report Properties from the context menu. This will display the Report Properties dialog box.
  2. On the Report Properties dialog box, click the Code tab. This will display the Custom code textbox.
  3. In the Custom code textbox, enter the following function declaration:
Public Function Divide(ByVal Numerator As Double, ByVal Denominator As Double, ByVal AltResult As Double) AS Double
If IsNothing(Denominator) Or Denominator = 0
Return AltResult
Else
Return Numerator/Denominator
End If
End Function

Once this is created, you can call it in your Expression like this:

=IIf(Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value) = 0, "", 
code.Divide(Sum(Fields!ID_Last_4_Weeks_Difference_ECNs_.Value/4),
Sum(Fields!ID_Last_4_Weeks_Previous_Year_ECNs_.Value/4), 0))

Notes:

  1. When calling the function, you must put "code" before your function name.
  2. The function call is saying, if zero is the Denominator, return zero as the result. You can supply any alternative number you want (e.g. -1).
  3. I am assuming that the issue is that the second SUM in the division (the denominator) is causing the issue. Zero as the numerator should return zero.
  4. Multiple functions can be created in the Custom code field. Just add the latest code to the top or bottom of the field.
  5. This is not verified code, so anyone is welcome to improve it.

I hope this helps someone.

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.