Forum Discussion
PowerBI - Direct Query & Cache
HI Guys,
I'm one happy student that likes to learn.
So, I'm stumble with PowerBI Desktop.
I have an Azure SQL which always get new dataset as often as the user wants.
I'm building report on PowerBI desktop and setup connection as "Live Connection"/"Direct Query".
Everyone is happy at this point of time.
Then the problem came, I can't seems to be able to get latest dataset from AzureSQL.
My brain expects that, every time I change slicer or re-open the PowerBI File, there will be query sent to Azure SQL to get latest data. But doesn't seems to be the case.
I tried to clear cache File -> Options and settings -> Settings -> Global -> Data Load -> Clear Cache.
Still my chart not refreshed.
I read online about "persistent cache", but have no idea how to clear it.
Please help me.... I want to see my latest data on my report.
cheers,
Freddy
14 Replies
Are you sure that No Report/Page Filters applied on report ?
Are you able to identify what data is missing in reportt?
Yes, i can identify what's missing, new row.
and yes, simple table viz, no filter, no slicer, nothing else.
FreddySetiawan , is is happing with all visual or only one visual. Also, what is version you are using? Did it happen before or happened first time
amitchandak all viz.
If I reopen the file, the data still as per cache.
I can only get new data IF 1) create new connection on new file
or 2) change to Import mode and press RefreshMy PowerBI Desktop version is: Version: 2.84.701.0 64-bit (August 2020).
May be worth to mentioned that the file has 2 connections.
1) Import
2) Live
and the column that I'm having trouble is a calculated column:MixColumn = LOOKUPVALUE('LIVE_TABLE'[TargetName],'LIVETABLE'[SourceKey],IMPORTED_TABLE[ClientKey])I was able to reproduce your issue on my machine as well using same version as you are using
Although I don't have any clear answer why it is happening but as soon as I click "Refresh" on the top of the screen of PBI Desktop or "Refresh data" in App service it brings latest data to the report.
Its sounds from your descriptions like you've setup a composite model (one that contains both imported and direct query sources). Is your calculated column trying to pull data from a direct query table onto the imported table where it was created?
As the others have noted - if it is a pure direct query report - the data would refresh automatically when new visuals or page actions are taken (or when time has elapsed if you set up automatic page refresh).Yes, it's composite model.
And you are absolutely right, my calculated column is doing lookup to the live table to get the latest value.
Think of master data use case.
The large fact table is remain static until weekend refresh, users are using data mapping tool to generate new mapping value and they want to see the new categorization near real-time (hence direct query).
Any idea how to for the calculated field to be recalculated? or set it to be non cache (if there's cache).
Hi FreddySetiawan ,
Under ‘Formatting’ on the visualizations pane, find ‘Page refresh’ and click the slider to ‘On’.
Here is the reference:
https://spreadsheeto.com/power-bi-data-refresh/
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
