Forum Discussion
Dealing with Null Values in a Conditional Formula
isblank is used to handle null
if ([Age] < 10 ,"0-9"
,if ([Age] >= 10 &&[Age] <= 20 ,"10-19"
,if ([Age] >= 20 &&[Age] <= 30 ,"20-29"
,if ([Age] >= 30 &&[Age] <= 40 ,"30-39"
,if ([Age] >= 40 &&[Age] <= 50 ,"40-49"
,if ([Age] >= 50 &&[Age] <= 60 ,"50-59"
,if ([Age] >= 60 &&[Age] <= 65 ,"60-64"
,if ([Age] > 64 ,"65+"
,if (isblank([Age]) , "N/a"
,"N/a"))))))))Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
- az386 years agoCommunity Champion
Anonymous
are you sure the problem is in Age Range? As I understand [Age] is custom column too..
and you have some miscontinious:
else if [Age] >= 10 and [Age] <= 20 then "10-19"
else if [Age] >= 20 and [Age] <= 30 then "20-29"What Age range should be for 20?
do not hesitate to give a kudo to useful posts and mark solutions as solution
- Anonymous6 years agoNot applicable
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.
- az386 years agoCommunity Champion
Anonymous
right click to field and "Replace Errors" 🙂
do not hesitate to give a kudo to useful posts and mark solutions as solution
- Anonymous6 years agoNot applicable
An error comes back with your solution:
"Token RightParen Expected"
- amitchandak6 years agoSuper User
if ([Age] < 10 ,"0-9" ,if ([Age] >= 10 &&[Age] <= 20 ,"10-19" ,if ([Age] >= 20 &&[Age] <= 30 ,"20-29" ,if ([Age] >= 30 &&[Age] <= 40 ,"30-39" ,if ([Age] >= 40 &&[Age] <= 50 ,"40-49" ,if ([Age] >= 50 &&[Age] <= 60 ,"50-59" ,if ([Age] >= 60 &&[Age] <= 65 ,"60-64" ,if ([Age] > 64 ,"65+" ,if (isblank([Age]) , "N/a" ,"N/a")))))))))- az386 years agoCommunity Champion
it's a DAX
Anonymous is trying to create a custom column in Power Query Editor
do not hesitate to give a kudo to useful posts and mark solutions as solution