cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## FIRSTDATE() and internal logic of DAX expressions

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:
CALCULATEAVERAGE('Table'[Value]), FILTER'Table', 'Table'[Period] = FIRSTDATE('Table'[Period]) ) )
altought it will not produce correct results either. Why no error though?
1 ACCEPTED SOLUTION
Solution Sage

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.

2 REPLIES 2
Community Support

Hi @mbidelski ,

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.

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.

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.

Solution Sage

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.