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
Anonymous
Not applicable

Direct Query and using CalculateTable

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]
)
))
1 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. 

081103.jpg

 

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.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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
Not applicable

@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.  

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. 

081103.jpg

 

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.

really helpful knowledge! Save my day and thanks for sharing

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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.