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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
fernandoC
Helper V
Helper V

IF formula to filter based on Fiscal Year

Hi!

 

I want to create an IF formula so that it filters based on a particular fiscal year. Also I want depending on the Fiscal Year a particular SUM on a table is triggered. 

 

This is the formula that I'm using (that doesn't work 😞 😞 

Training Hours Test = CALCULATE(IF(ISFILTERED('Fiscal Year Calendar'[Fiscal Year]) = "FY19-20",SUM('Chisinau 2 FY1920'[# of Employees]),SUM('Chisinau 2 FY2021'[# of Employees]) ))
 
it gives me the following error:  "Dax Operations do not support comparing values of type TRUE/FALSE with values of type text".
 
 
These are the two tables that I want to trigger when a particular fiscal year is filtered : IF FY19-20 then SUM # of Employees from Table 1 OR IF FY20-21 then SUM # of Employees from table 2. (Would like to do this not only for these fiscal years but for any subsequent one i.e FY21-22, etc.)
 

calendar table.PNG

1 ACCEPTED SOLUTION

@fernandoC Oh, sorry, missed that in the formula, was so focused on the other issue, you probably want:

IF('Fiscal Year Calendar'[Fiscal Year] = "FY19-20",CALCULATE(SUM('Training Costs'[# of Employees])))

or:

IF(MAX('Fiscal Year Calendar'[Fiscal Year]) = "FY19-20",CALCULATE(SUM('Training Costs'[# of Employees])))

 

ISFILTERED returns true/false if the column is filtered or not. So, you can't compare that to a text value. That is what it was complaining about. Use the second version if this is a measure. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@fernandoC First, I think you want:

Training Hours Test = IF(ISFILTERED('Fiscal Year Calendar'[Fiscal Year]) = "FY19-20",CALCULATE(SUM('Chisinau 2 FY1920'[# of Employees]),SUM('Chisinau 2 FY2021'[# of Employees]) ))

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler,

 

Thank you for your quick response. 

 

I've made some changes to both tables and appended them altogether as I needed it for other calculations. Based on your formula I changed it to: 

IF(ISFILTERED('Fiscal Year Calendar'[Fiscal Year]) = "FY19-20",CALCULATE(SUM('Training Costs'[# of Employees])))
 
When trying this formula I still get the error: Dax comparison operations do not support comparing values of type true/false with values of type text. Also, would like to be able to have multiple filters in the formula such as FY19-20,FY20-21,FY21-22 if at all possible. 
 
This is what it looks like now:
 
Name of table: 'Training Costs'
 
FY Total.PNG

 

@fernandoC Oh, sorry, missed that in the formula, was so focused on the other issue, you probably want:

IF('Fiscal Year Calendar'[Fiscal Year] = "FY19-20",CALCULATE(SUM('Training Costs'[# of Employees])))

or:

IF(MAX('Fiscal Year Calendar'[Fiscal Year]) = "FY19-20",CALCULATE(SUM('Training Costs'[# of Employees])))

 

ISFILTERED returns true/false if the column is filtered or not. So, you can't compare that to a text value. That is what it was complaining about. Use the second version if this is a measure. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks Greg! it works. 

fernandoC
Helper V
Helper V

For some reason the tables were not attached on my original comment:

 

FY1920.PNGFY2021.PNG

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.