Forum Discussion
Dealing with Null Values in a Conditional Formula
Yes. Your are correct. I've fixed it. Values come in with no greater than 6 decimal places so I've adjusted the equation to account for all ranges:
if [Age] < 10 then "0-9"
else if [Age] >= 10 and [Age] <= 20 then "10-19"
else if [Age] >= 20.000000001 and [Age] <= 30 then "20-29"
else if [Age] >= 30.000000001 and [Age] <= 40 then "30-39"
else if [Age] >= 40.000000001 and [Age] <= 50 then "40-49"
else if [Age] >= 50.000000001 and [Age] <= 60 then "50-59"
else if [Age] >= 60.000000001 and [Age] <= 65 then "60-64"
else if [Age] > 64.000000001 then "65+"
else if [Age] = null then "N/a"
else "N/a"
You are also correct in that both Age and Age Range are calculated fields. Age Range is based upon the Age column while the Age column is dependent on the DOB column. The DOB column is not a calculated field. DOB is simply a numerical field in its original form when first brought in.
The issue I am having is that DOB has some instances where it is blank. When it is transformed to Age...null values result. Then, the Age Range field (and my calculation) cannot place a null value in a range...resulting in errors. I want these errors to be given values of "N/a" and I do not know how to do that.
Anonymous
right click to field and "Replace Errors" 🙂
do not hesitate to give a kudo to useful posts and mark solutions as solution
- az386 years agoCommunity Champion
Anonymous
is Replace error function a good solution for you as I see here https://community.powerbi.com/t5/Desktop/how-to-replace-error-in-query-editor/m-p/490379#M228507 ?
do not hesitate to give a kudo to useful posts and mark solutions as solution