cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FreddySetiawan
Advocate II
Advocate II

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 14
swise001
Continued Contributor
Continued Contributor

@FreddySetiawan 

 

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

swise001
Continued Contributor
Continued Contributor

@FreddySetiawan 

Thank you for explaining.  I would suggest that you try to solve your problem by using a DAX Measure that populates on the report canvas - rather than a calculated column on your imported table.  With a measure - the value would be recalculated each time the page is refreshed or when a query is sent back to the data source.  This will likely require some retweaking of your DAX - since your calculated column formula won't necessarily transfer 1:1 to a report canvas measure.  Consider that it may not be possible at all - depending on its complexity (as direct query will only allow DAX that can be converted to T-SQL).  

Alternatively - you can explore creating a view at the data source that can similarly be queried for your model.  

@swise001 

Thanks for the suggestion but we can't use measure.

Anyway, I perform simple testing on Power BI.

 

I have 3 fields (2 column, and 1 measure)

the formula is just a simple NOW() the diffence between them is location where they created.

 

now_DirectQuery = NOW()
now_imported_column = NOW()
now_Imported_Measure = NOW()
 

 

FreddySetiawan_0-1597904209031.png

 

as you can see above, when I force refresh "Refresh Visuals" only now_imported_Measure and now_DirectQuery  is refreshed.

Interesingly if we ammend the now_imported_column by adding space "now(    )" or whatever dummy changes, the viz is updated.

 

I read https://docs.microsoft.com/en-us/dax/now-function-dax

and there's a remark "The result of the NOW function changes only when the column that contains the formula is refreshed. It is not updated continuously."

 

but I want to know how to force it to be updated...

 

hummmmm......

Hi @FreddySetiawan ,

 

Under ‘Formatting’ on the visualizations pane, find ‘Page refresh’ and click the slider to ‘On’.

page-refresh-on.png

Here is the reference:

https://spreadsheeto.com/power-bi-data-refresh/

 


Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

hi @v-kelly-msft ,

I tried but it doesn't refresh the viz.

the calculated column still remain as the first time we create the column.

the only way we can get so far is by changing the column formula, like adding dummy space, etc.

amitchandak
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

 

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

 

 







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

Proud to be a Super User!




@FarhanAhmed thanks for helping me repro the issue.

I can't click Refresh as my Live dataset is pretty huge... and refresh data is not available on DirectQuery table.

Have you tried change detection Page Refresh option in Power BI, I think this will help you get refreshed data.

 

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-2020-feature-summary/

 

 







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

Proud to be a Super User!




@FarhanAhmed I did, but it doesn't change anything.

I enabled performance analyzer and see that there's update every now and then when there's a change.

I also start DAX Studio and monitor there. indeed there's query running to check.

 

However I think it boils down to the fact that the calculated column is not recalculated.

 

If I create new viz just using dataset from DirectQuery table, I can see the new rows.

But on my ImportedTable calculated column, it doesn't show anything, until... I change something on the formula

FarhanAhmed
Super User
Super User

Are you sure that No Report/Page Filters applied on report ?

Are you able to identify what data is missing in reportt?







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

Proud to be a Super User!




@FarhanAhmed 

Yes, i can identify what's missing, new row.

and yes, simple table viz, no filter, no slicer, nothing else.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors
Top Kudoed Authors