cancel
Showing results for 
Search instead for 
Did you mean: 

Filter() causing Composite Model to run Direct Query over DAX Query

I have created a composite model with aggregations of my sales tables. When querying sales (SUM() of a column) by year in a table, as expected a DAX query is ran against the aggregated table. If in my DAX formula I calculate sales for a chosen year, if I do not use a filter argument

 

Sales 2019 =
CALCULATE( [sales],
'Calendar'[Year] = 2019 )
 

then a DAX query is running as expected against the aggregated table.

When I use a filter argument on the calendar table to calculate it in the same way, this then runs a Direct Query when it is not needed.

Sales 2019 =
CALCULATE( [sales],
FILTER( 'Calendar', 'Calendar'[Year] = 2019 ) )
 

I understand the filter argument is not needed in this situation, but more complex situations make it unavoidable. The calendar table is set to dual storage mode, so is capable of not running the Direct Query.

 

Is there any advice around this or reasoning as to why this is happening?

Status: New
Comments
Moderator

Hi @craigrg96

 

Would you please record a video to show the issue clearly? Please don't contain sensitive information in the video. 

 

Best Regards,
Qiuyun Yu