Trying to add a filter using calculated column

[Revati25]

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?

[v-saisrao-msft]

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,

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.

[v-saisrao-msft]

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.

[v-saisrao-msft]

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.

[v-saisrao-msft · Accepted solution]

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.

[bhanu_gautam]

@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]
)

[Revati25]

I tried checking, there are no zeros encountered