Forum Discussion
Advocate ITrying to add a filter using calculated column
I am trying to create a filter using a calculated column,
The calculation is used for bifercation of data based on a Measure, before it was only taking one condition and now it is giving an Error "A division by zero has encountered". Following is the calculated column I am trying to create
SWITCH(
TRUE(),
[Exclude Utilization] <= 75, "Less than 75%",
[Exclude Utilization] >=75 && [Exclude Utilization] <= 100 , "75% - 100%",
[Exclude Utilization] >=100 && [Exclude Utilization] <= 120 , "100%- 120%",
[Exclude Utilization] >=120 && [Exclude Utilization] <= 140 , "120%- 140%",
"Above than 140%")
The measure being used has different measures used for calculation, is that the possible reason for the calculated column not taking other conditions?
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.
8 Replies
- bhanu_gautam
Super User
Revati25 Review the calculation of the [Exclude Utilization] measure to ensure that there are no divisions by zero. You can add checks to handle cases where the denominator might be zero.
DAX
Exclude Utilization =
IF(
[Denominator] = 0,
BLANK(),
[Numerator] / [Denominator]
)
Revati25I tried checking, there are no zeros encountered
v-saisrao-msftCommunity Support
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.
- Revati25
I have tried both methods, using IF condition and changing the bounds as well, no impact was seen
- v-saisrao-msft
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.
