Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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))
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
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.
Expression examples:
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
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.
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.
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:
I hope this helps someone.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |