why can't I use firstdate() in a filter expression

[Anonymous]

I received an error message with the DAX message below:

New Hires2 =
CALCULATE (
DISTINCTCOUNT ( UserDirectory[USERID] ),
UserDirectory[Hire Date]
>= FIRSTDATE('Calendar'[Date] )
&& UserDirectory[Hire Date]
<= LASTDATE('Calendar'[Date] ))

Error message: A function 'FIRSTDATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I have replaced firstdate() with min() which doesn't work either. Why can't i use functions in a filter condition?

[v-xiaotang · Accepted solution]

Hi @Anonymous 

CALCULATE(<Expression>[,<Filter>[,<Filter>[,...]]])
Filter:A boolean (True/False) expression or a table expression that defines a filter.

FYI: https://dax.guide/calculate/ 

So, you can use a boolean (True/False) expression like this:

New Hires2 = 
VAR _firstdate =
    FIRSTDATE ( 'Calendar'[Date] )
VAR _enddate =
    LASTDATE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( UserDirectory[USERID] ),
        UserDirectory[Hire Date] >= _firstdate
            && UserDirectory[Hire Date] <= _enddate
    )

OR

use a table expression like this:

New Hires2 =
CALCULATE (
    DISTINCTCOUNT ( UserDirectory[USERID] ),
    FILTER (
        ALL ( UserDirectory ),
        UserDirectory[Hire Date] >= FIRSTDATE ( 'Calendar'[Date] )
            && UserDirectory[Hire Date] <= LASTDATE ( 'Calendar'[Date] )
    )
)

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

CALCULATE accepts boolean expressions or table expressions that define filters, or filter modifier functions.
You can modify your measure as follows using variables:

New Hires2 =
var __mindate = min('Calendar'[Date] )
var __maxdate = max('Calendar'[Date] )
return
    CALCULATE (
        DISTINCTCOUNT ( UserDirectory[USERID] ),
        UserDirectory[Hire Date] >= __mindate && 
        UserDirectory[Hire Date] <= __maxdate
    )

[Anonymous]

Thanks! I enjoy watching the tutorial. It's very helpful.

[Anonymous]

Sorry, I'm proably a pain in the neck. What do you mean that CALCULATE accepts boolean expressions? Does that mean I can not wrap a function (e.g., min()) in a CALCULATE expression?

[Fowmy]

@Anonymous 

I would like to share some links for you to understand the concepts better:

https://dax.guide/calculate/
https://www.youtube.com/watch?v=Tk-7gBt9CDE&list=PLU6II7MW-aiIees6mrPfdjt9c8noi7P66