cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Direct Query not refreshing when used within Union

We have two data sources setup in PBI.  One source is imported directly into PBI for historical data.  Another data source is using a direct query to get results from SQL (via on premise gateway) for today's data or live data.  We then created a union to merge the two tables together and present it as one table to the user. 

 

MergedTable = Union('history','live)

 

The problem we are having is that the direct query does not refresh automatically when the user refreshes the page.  If we add the live table to the dashboard as a new tile, it will refresh as expected.  the problem is that if used within the union it isn't refreshing without a full dataset refresh either from the web or the desktop application.

 

Please note:  Refreshing the entire dataset is not an option as it is quite large and takes about 20 mins to refresh.  We just need the direct query to refresh within the union to bring in live data.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Direct Query not refreshing when used within Union

@kenneth_hess , Have set up refresh for this table? - Please refer to Microsoft doc -

Calculated tables

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.

 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: Direct Query not refreshing when used within Union

@kenneth_hess This is a known issue because you are in composite mode. I'll try to dig up the technical explanation, I had it at some point. But, bottom line, this is expected behavior actually if my memory serves.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IX
Super User IX

Re: Direct Query not refreshing when used within Union

@kenneth_hess , Have set up refresh for this table? - Please refer to Microsoft doc -

Calculated tables

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.

 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors