Forum Discussion
Helper IWhat is wrong with this CALCULATE formula
Hello! I need a 2nd pair of eyes on this Measure formula. I am new to using CALCULATE and am struggling a bit.
The table is called 'CDM Study metrics view'. The [Met GoLive Deadline] variable value is either null or "Yes".
What I want to do is count how many "Yes" there are when the Go Live Year is 2024. I've gone over and over this and can't figure out what I'm doing wrong. My error message is "The expression specified in the query is not a valid table expression".
Is it OK to use COUNT in a calculate function?
The filter function is returning a table, when you used it with a calculated, you have to use it only one time, instead of many calls of the function,
So the same code could be replaced by
Met GoLive 2024 =
CALCULATE(
COUNT('CDM Study Metrics view'[Met GoLive Deadline]),
FILTER(
'CDM Study Metrics view',
'CDM Study Metrics view'[Met GoLive Deadline] = "Yes" && 'CDM Study Metrics view'[Go Live Year] = 2024
)
)I'm just using filter one time and using an AND to mix the condition,
As you are a beginner, you can also rembember that calculate don't need a filter call, so the following dax function should also work
Met GoLive 2024 =
CALCULATE(
COUNT('CDM Study Metrics view'[Met GoLive Deadline]),
'CDM Study Metrics view'[Met GoLive Deadline] = "Yes",
'CDM Study Metrics view'[Go Live Year] = 2024
)And for your second question, YES, count can be used with calculate function
2 Replies
- Cookistador
Super User
The filter function is returning a table, when you used it with a calculated, you have to use it only one time, instead of many calls of the function,
So the same code could be replaced by
Met GoLive 2024 =
CALCULATE(
COUNT('CDM Study Metrics view'[Met GoLive Deadline]),
FILTER(
'CDM Study Metrics view',
'CDM Study Metrics view'[Met GoLive Deadline] = "Yes" && 'CDM Study Metrics view'[Go Live Year] = 2024
)
)I'm just using filter one time and using an AND to mix the condition,
As you are a beginner, you can also rembember that calculate don't need a filter call, so the following dax function should also work
Met GoLive 2024 =
CALCULATE(
COUNT('CDM Study Metrics view'[Met GoLive Deadline]),
'CDM Study Metrics view'[Met GoLive Deadline] = "Yes",
'CDM Study Metrics view'[Go Live Year] = 2024
)And for your second question, YES, count can be used with calculate function
colettbThank you so much! This is very helpful!!!