Visuals with calculated columns/tables fail during semantic model refresh

[FabricEngineer]

Hi everyone!

I have a question about semantic model refresh and their effect on the connected reports in fabric workspaces. I have a semantic model that contains tabular model in it in import mode and there are thin reports connected to it in my fabric workspace. What I want to do is to process my tables in semantic model as new data arrives so that reports connected to it will be updated.

So far, I use data pipeline artifact in fabric and use semantic model refresh (preview) activity in it.

I have following settings for it:

I had to use partial batch instead of transactional as a commit mode, because I get memory errors in transactional mode. The same error occurs when I go to workspace lineage and refresh semantic model, so that was my only option to refresh semantic model.

In the end, this approach refreshes my data in the report. The problem is that during the semantic model refresh, all visuals that use any calculated column/table fails with the similar error as follows:

I would like to be able to use the report with no errors during semantic model refresh. Should I change the setting of semantic model refresh activity or is there any alternative way to deal with that?

Thanks in advance for your help!

[suparnababu8]

Hi @FabricEngineer 

I recommend you create that calculated column in Power Query and try to refresh again. Could you please go through below urls which might hels you

Solved: Report refresh and calculated column changes takes... - Microsoft Fabric Community

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

Refresh Power BI Semantic Model After Dataflow Automatically - RADACAD

Thanks!

[FabricEngineer · Accepted solution]

Hi @suparnababu8 

Thanks for the links. I think they will help me to make it more optimized during the refresh.

After some investigations, I realized that not just calculated columns/tables, also the relationships get broken during semantic model refresh, if a visual uses several tables with relationship.

I did some experiments and I realized that the problem was because I chose "partial batch" as commit mode. This was causing the tables under process to not function in the report. Previously, I was not able to use "transactional" commit mode due to memory issues since I was trying to process all tables in one semantic model refresh activity. 

Now I tried using 3 different semantic model refresh activites with "transactional" commit mode connected together and they refresh different tables in semantic model to decrease the load on memory at an instant time. This solved my problem and currently no visuals fail in my report during any of these semantic model refresh activities. I don't exactly know how "partial batch" causes these problems in report but for now, "transactional" commit mode solved my problem.

Thanks again for your comment!

[v-veshwara-msft]

Hi @FabricEngineer ,

Glad to hear that the issue with the semantic model refresh has been resolved.

The "partial batch" commit mode seemed to be causing problems with relationships and calculated columns/tables, likely due to incomplete data commits. By splitting the refresh into multiple activities with "transactional" commit mode, the memory load has been reduced and model has remained consistent, which appears to have resolved the errors.

It’s good to know everything is working smoothly now.

If any further assistance is needed, feel free to reach out.

It might be helpful to explore these additional resources to further optimize your refresh process and troubleshoot any potential issues:
Troubleshoot refresh scenarios - Power BI | Microsoft Learn
Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

Please mark your solution or any other helpful responses as "Accept as Solution" to assist others with similar queries.

Best Regards,
Vinay.

[ibarrau]

Hi. That's strange. Can you make sure the semantic model is refreshing in desktop or service now? I'm thinking a column could have been removed and now a calculated table is failing. Let's check if other places works and it's something about the new activity or if it has a failure.

I hope that helps,

[FabricEngineer]

Hi @ibarrau 

The semantic model is refreshing properly on partial batch commit mode, which lasts around 10 mins. During that time the visuals with calculated columns break. But once the refresh is completed, then the report visuals work properly again. The tables and column definitions are the same as I checked.