cancel
Showing results for
Did you mean:

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

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.)

1 ACCEPTED SOLUTION
Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
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]) ))``

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper V

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'

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper V

Thanks Greg! it works.

Helper V

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors