Direct Query not refreshing when used within Union

[kenneth_hess]

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.

[amitchandak · Accepted solution]

@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

[Greg_Deckler]

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