Forum Discussion
Trying to add a filter using calculated column
Hi Revati25,
It appears the issue arises because Power BI doesn't support using measures directly in calculated columns, as they function at a row-level context. A potential solution is to create a calculated column for Exclude Utilization using the actual column values instead of the measure.
Additionally, ensure the denominator has no zeros or blanks to prevent further errors. If the logic is only required for visuals, consider using a Measure instead, which is more efficient.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi Revati25,
Thank You for reaching out to the Microsoft Forum Community.
bhanu_gautam has provided helpfull solution to address the "A division by zero has been encountered" error by using the IF() function to avoid issues when the denominator is zero. This is a solid approach and should effectively resolve the error.
In addition to that, I wanted to share a couple of additional tips that might be helpful:
- While the IF() function is effective, you might also want to consider using the DIVIDE() function. It is specifically designed to handle division by zero without requiring additional conditional checks. DIVIDE() takes a numerator, a denominator, and an optional value to return if the denominator is zero, making it a cleaner and often more efficient alternative.
- Using >= for both lower and upper bounds in your calculated column can cause overlapping conditions, leading boundary values like 75 or 100% to match multiple categories. Replacing >= with > for the lower bounds will ensure each value fits into only one category, preventing errors.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
I have tried both methods, using IF condition and changing the bounds as well, no impact was seen
Hi Revati25,
It appears the issue arises because Power BI doesn't support using measures directly in calculated columns, as they function at a row-level context. A potential solution is to create a calculated column for Exclude Utilization using the actual column values instead of the measure.
Additionally, ensure the denominator has no zeros or blanks to prevent further errors. If the logic is only required for visuals, consider using a Measure instead, which is more efficient.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi Revati25,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi Revati25,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi Revati25,
We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.
Thank you.