Direct Query and using CalculateTable

[Anonymous]

I'm confused.   I thought a direct query model would not allow the DAX creation of a table using CALCULATETABLE.   I created a formula using that function in the fomula below, in a direct query model, with no problem.   Am I understanding the documentation correctly?    

• Calculated tables aren't supported: The ability to define a calculated table using a DAX expression isn't supported in DirectQuery mode.

PYTD Net Shipped Revenue =

IF (

[ShowValueForDates],

CALCULATE (

[YTD Net Shipped Revenue],

CALCULATETABLE (

DATEADD ( 'Dim_Date Table'[Date], -1, YEAR ),

'Dim_Date Table'[DatesWithShipDates]

)

))

[amitchandak]

@Anonymous , I am not fine with complete statement

But you can try like

PYTD Net Shipped Revenue =
IF (
[ShowValueForDates],
CALCULATE (
[YTD Net Shipped Revenue],
DATEADD ( 'Dim_Date Table'[Date], -1, YEAR )
))

[Anonymous]

@amitchandak   Thanks.   I was more asking about the ability of Direct Query to handle the CALCULATETABLE function.    The Microsoft documentation seems to suggest that direct query model cannot support this functionality, but I am using it with no probem.   So that is where it seems confusing.  

[v-jingzhang · Accepted solution]

Hi @Anonymous 

It's an interesting question I have never thought about. I read some official documents again and found below descriptions. 

Calculated tables are recalculated if any of the tables they pull data from are refreshed or updated, unless the table uses data from a table that uses DirectQuery; in the case with DirectQuery, the table will only reflect the changes once the dataset has been refreshed. If a table needs to use DirectQuery, it's best to have the calculated table in DirectQuery as well. (From Using calculated tables in Power BI Desktop - Power BI | Microsoft Docs)

You can add calculated tables to a model that uses DirectQuery. The Data Analysis Expressions (DAX) that define the calculated table can reference either imported or DirectQuery tables or a combination of the two.

Calculated tables are always imported, and their data is refreshed when you refresh the tables. If a calculated table refers to a DirectQuery table, visuals that refer to the DirectQuery table always show the latest values in the underlying source. Alternatively, visuals that refer to the calculated table show the values at the time when the calculated table was last refreshed. (From Use composite models in Power BI Desktop - Power BI | Microsoft Docs)

It seems you are using CALCULATETABLE in a measure. If you put this measure in a visual, I think it will always query the latest data from the data source and do a calculation on that. When we say the calculated table, it refers to a table created using the New table feature under Modeling rather than the CALCULATETABLE function in DAX. A calculated table refers to a table defined by a DAX formula. 

Hope this can help make it clear. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[aa333bb]

really helpful knowledge! Save my day and thanks for sharing