Forum Discussion
PowerBI - Direct Query & Cache
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!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.
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.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()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......
