calculated tables do not update

[Anonymous]

I have a calculated table that uses most recent data to calculate a metric. This worked previously, but now when I refresh my data connection, the calculated table does not update.

For example. I refreshed my data on Jan 3, and the table recalculated. I refreshed my data Jan 10, but the data in the calculated table still referred to Jan 3.

Is there a way to refresh all calculations in powerbi? Am I doing something incorrect?

[IAMQ]

I figured out how to do this but it's not pretty and really only possible with a Premium Per Capacity sku.

If you have PPC, you can connect to the XMLA endpoint in SSMS. Open the semantic model and go to tables, then right click on your calculated table and select "Process Table".

In the dialog box that opens just select "Proces Full" and then choose the Scrip button at the top and choose "Script Action to Job".

Set the SQL server agent job to execute every few minutes and it will now refresh the table on that cadence. 

1. This is NOT a good solution.

2. This only is useful in very SPECIFIC use cases. 

3. IS NOT AN IMMEDIATE REFRESH - This in itself makes it usless for when filtering would or should change the values of a table. 

In my case, I have a table with each user's email and the specific ID that each is entitled to view in the dataset. This is referenced in dynamic row level security rules. The only way I could handle the instances where the same user would have access to more than one ID and not every ID was to take that table and create a calculated one using FILTER. This would allow me to use

[SalesGroupID] IN VALUES (CalculatedTableName[SalesGroupID]) as a the DAX rule in RLS. For my use case, it was okay if the refresh only happened every 5 minutes or so. There wouldn't be many instances where we would need to update someone's access to an individual sales group. If that does occur then it's completely reasonable for us to allow up to 5 minutes for the table to refresh. 

If I could just figure out how to use VALUES (where it accepts a column) and filter that column, that would eliminate my need. However, all the filtering functions return a table and I can't take the result and grab a specific column from that function's return table. What the calculated table does is allow me to pass in the column by itself, but the only rows in that table belong to the logged in person. (i.e. CalculatedTable = FILTER(RowLevelSecurityTable, [UserEmail] = USERPRINCIPALNAME()) )

I only give backround to explain why I am using such a messy process - it's a substitute for the requisite DAX skills 😁

[HouseOfMax]

I have the same issue, but only for some of my calculated tables.

I have a data model that used both direct queries and imported tables, and the calculated tables that use imported data seem to update when the source is manually refreshed, however, the calculated tables that have direct queries as their source don't update when the source is automatically updated.

Merged tables (from imports) don't automatically update when the originals are updated either, they need to be manually refreshed.

Are these issues related to using a composite model, or am I doing something wrong? And is there a way around it?

Thanks!

[d_gosbell]

---

@HouseOfMax wrote:

however, the calculated tables that have direct queries as their source don't update when the source is automatically updated.

---

This is just how calculated tables work. They are stored in-memory (like imported tables) and are only updated when you do a refresh operation. If you need realtime data do not use calculated tables, you would have to look at some other option like doing a view in your source database. 

[Anonymous]

This is an old post , but still I wanted to know if you found any solution for the problem.

I am facing the same issue using Calculated tables.

[d_gosbell]

Can you provide steps to reproduce your issue?

In theory this should be impossible as any form of refresh should trigger a refresh of all calculated tables (this is one of the down sides of using calculated tables in large models)

[v-yulgu-msft]

Hi @Anonymous,

Please make sure there existing any update to the data source after Jan 3.

How did yo refresh dataset? Did you click the refresh button in Query Editor mode or right click the table to choose "Refresh Data" in data view?

How did you create the calculated table? If possible, please provide more details about repro steps.

Regards,

Yuliana Gu

[themistoklis]

@Anonymous

How do you refresh your data? Through Power Query Editor?

There you have the option to Refresh a Preview or Refresh All tables.

Do you also have any filters applied to the data?