Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mbidelski
Helper I
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
daXtreme
Solution Sage
Solution Sage

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.

View solution in original post

2 REPLIES 2
v-rongtiep-msft
Community Support
Community Support

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
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors