Deployment Pipeline - Error with calculated tables (dataset refresh is mandatory ?)

[vivien57]

Hello everyone,

I am experiencing a problem with deployment pipelines similar to what is described here but the solution does not work.

https://community.fabric.microsoft.com/t5/Developer/Deploying-the-Data-in-Pipelines-new-calculated-c...

I have a simple dashboard (built for the example) where I have a table calculated :

Here's the context : 

• I have two WKSPs (DEV / UAT)
• A deployment pipeline between the two WKSPs
• Both wksp already contain the same version of the dashboard, connected to the data source and both dashboards are working.

The actions I take :

• I modify the dashboard in Power BI Desktop (I delete or add a value in the calculated table)
• I make a publish in DEV's Wskp, the dashboard is functional with the modification
• then I run the DEV deployment pipeline to UAT.

And then... the dashboard no longer works with the following error:

For the error to disappear, I need to refresh the underlying dataset, but that's unacceptable because it means a break in service for users (even the PBI app is broken as long as the refresh dataset hasn't been run).

Do you have a solution? Is it a problem of product maturity on this feature?

Thanks in advance,

Vivien

[vivien57]

While I am waiting for you to get back to me, I have come up with the following idea:

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=f4c053bc-2bb9-ef11-95f6-6045bdb14c23

Feel free to put a like 🙂

[Anonymous]

Hi All,
Firstly @powerbiexpert22  thank you for your solution!

And @vivien57 , Based on your description, the core reasons that trigger this are
Calculation tables rely on dataset refreshes, and the deployment pipeline does not automatically refresh datasets by default after publishing changes to the target environment. Failure to refresh causes the calculation table to be empty and affects dashboard loading.
Here are some solutions I have found for you：1. Move the logic for calculating tables to a data source or Power Query to avoid relying on dataset refreshes after deployment.
2. Perform the following actions after the deployment is complete:
Configure a timed refresh schedule to minimize the time window caused by empty data in the calculation table.
For example: Configure a 5-minute refresh interval in the target environment
Get started using deployment pipelines, the Fabric Application lifecycle management (ALM) tool - Mic...
3. Method 1: Automatically Refresh Datasets
Through Power Automate or the REST API, automatically trigger a dataset refresh after the deployment is complete to ensure that the calculation tables are calculated properly.
Implementation Steps:
Configure a Power Automate process to listen for deployment completion events.
Automatically refresh the dataset of the target environment using the Power BI connector (or HTTP call to the REST API).
Main steps of the Power Automate process:
(1): Create triggers (e.g., timed triggers or manual triggers).
(2): Specify the target workspace and dataset using Power BI's “Refresh Dataset” action.

Datasets - Refresh Dataset - REST API (Power BI Power BI REST APIs) | Microsoft Learn

All of the above methods avoid this problem by allowing your dataset to be refreshed automatically.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[vivien57]

Hello @Anonymous ,

Thank you for all these solutions, some of which may be interesting.

However :

• Not everything that is done in a calculated table can be done in PowerQuery or at data source level.
  
  
• As for the other solutions, including the API call to launch an automatic refresh after deployment, this doesn't really solve the problem. If the dataset takes 20 minutes to refresh, for example, users won't be able to use the dashboard for 20 minutes, which is not acceptable.

In my opinion, Microsoft must be able to offer a corrective solution that enables immediate deployment. We are in a CI/CD scenario, and we can see that automation isn't possible and therefore can't be industrialised on the scale of an organisation.

Are you in a position to raise this point with the Microsoft team ?

Thank in advance for your return,

Have a nice day,

Vivien

[powerbiexpert22]

Hi @vivien57 , try to run on demand refresh (manual refresh) on semantic model and see if everything works fine. if yes then run deployment pipeline again

[vivien57]

Hello,

Thanks for the feedback.

Yes, after a refresh it works, but until the refresh is complete, the dashboard is not available, which is unacceptable from a user point of view.