Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pablo_Quintana
Frequent 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
Super User

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
Super User

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



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
Super User

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.