FIRSTDATE() and internal logic of DAX expressions

[mbidelski]

Hello everyone, I'm trying to understand some DAX logic, my first question would be:

Why does this expression work correctly: 

CALCULATE( AVERAGE('Table'[Value]), FIRSTDATE('Table'[Period]) )

In the documentation it says that FIRSTDATE returns a table with a single row in it - so how does CALCULATE know how to use it correctly?

Next question - this expression will return an error:

CALCULATE( AVERAGE('Table'[Value]), 'Table'[Period] = FIRSTDATE('Table'[Period]) )

but this will not:

CALCULATE( AVERAGE('Table'[Value]), FILTER( 'Table', 'Table'[Period] = FIRSTDATE('Table'[Period]) ) )

altought it will not produce correct results either. Why no error though?

[Anonymous]

Hi @mbidelski ,

Please learn more about FIRSTDATE: Returns the first date in the current context for the specified column of dates.

this expression will return an error: A function 'FIRSTDATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed. 

The error is caused by using a TRUE/FALSE expression, something that evaluates to TRUE or FALSE, to filter the table in a way that CALCULATE or CALCULATETABLE doesn’t support.  So the error is saying you can’t use a boolean comparison to filter your table except in very specific circumstances.

More details : DAX error: A function ‘XXXX’ has been used in a True/False expression that is used as a table filter... 

Why no error though. Please learn more about measure and column.

Calculated columns (and tables) are:

- Evaluated for each row in your table, immediately after you hit 'Enter' to complete the formula

- Saved back into the model so take up space

Calculated Measures are:

- Evaluated when you use it in a visual, when the visual is rendered

- Not saved anywhere (well, actually there's a cache in the report layer but it's not part of the file when you hit Save)

Generally, measures are more useful, but the trade-offs are the performance hit (report runtime vs. pre-processed), storage space, and the type of expressions you can use. For example calculated columns are often used when you want to filter on the result rather than just as a calculated result.

calculated columns and measures in dax 

Best Regards

Community Support Team _ Polly

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

[daXtreme · Accepted solution]

The easiest answer is here:

https://dax.guide/calculate

and here

https://dax.guide/firstdate

FIRSTDATE is a tricky function because it automatically performs context transition, even without being wrapped in CALCULATE. This explains why the last thing works but produces a completely wrong answer.