cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pablo_Quintana
Regular Visitor

IF + query problem

Hi everyone,

I have tried a lot of previous solutions I have found to this issue but I'm not being able to solve the IF problem

 

1. Recode birth year into age by 2020 - age :

Age = IF(BYear = Blank(), #Null or blank?#, (2020 - BYear))

The year is in number format, not date.

 

2.Recode this result into an interval:

 

IF(Age < 35, "<35 Years", IF(Age < 45, "Between 35 and 45 years",">45"))

 

(Both cases there something wrong with IF even if variables format are ok )

Thanks for your help 

 

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

Hi @Pablo_Quintana ,

 

You can make this on the query editor or adding a column in DAX:

Power Query:groupin

if  [BYear] = null then null else (2020- [BYear])

DAX:

Column = IF('Table'[BYear] = BLANK();BLANK();2020 - 'Table'[BYear])

 

Then you can make a group or new column wiht the following syntax:

Column 2 = SWITCH(TRUE();'Table'[Age] = BLANK();BLANK(); 'Table'[Age] < 35; "<35 Years"; 'Table'[Age] < 45 && 'Table'[Age] > 35; "Between 35 and 45 years";">45")

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User III
Super User III

Hi @Pablo_Quintana ,

 

You can make this on the query editor or adding a column in DAX:

Power Query:groupin

if  [BYear] = null then null else (2020- [BYear])

DAX:

Column = IF('Table'[BYear] = BLANK();BLANK();2020 - 'Table'[BYear])

 

Then you can make a group or new column wiht the following syntax:

Column 2 = SWITCH(TRUE();'Table'[Age] = BLANK();BLANK(); 'Table'[Age] < 35; "<35 Years"; 'Table'[Age] < 45 && 'Table'[Age] > 35; "Between 35 and 45 years";">45")

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Sry for being so late, I haven't been working on this the last week, well I have tried both solutions but there's something wrong, even if the formula it's working, Power Bi warn me about an error in the IF or Switch clause, but the column has the values, which is extremely weird.

Hi @Pablo_Quintana ,

 

What is the error you are getting can you share a print screen?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



amitchandak
Super User IV
Super User IV

@Pablo_Quintana , Try like

 

Age = IF( isblank([BYear]) , blank(), (2020 - [BYear]))

 

Switch ( True(),
[Age] < 35, "<35 Years",
[Age] < 45, "Between 35 and 45 years"
,">45")

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors