Semantic model not refreshing with synapse due to calculated columns?

[Rbakker888]

Hi, I recently joined a new company and we made 1 little change to the synapse scripts so we would have 1 extra column in our PowerBI semantic model, when we tried to update the model tho, it gave errors about columns that werent included in synapse for some reason so to fix this issue I instead made these columns myself in the powerBI semantic model with some DAX formulas cause they were based on data in the model. Now when I try to refresh the model it still tries to update those columns and breaks on that. Would there maybe be a way to ensure it doesnt try to update these columns when resfreshing the model or is there another way to fix this? 

[Anonymous · Accepted solution]

Open the pbix file, look at the Steps (or M code) for the query/table and see if any steps are referencing those columns. Sometimes there is a step that removes columns and that is usually where this issues arises.

[Rbakker888]

Thank you both, the removed column fixed my first issue and the permissions in synapse caused the second issue, now with more permissions in synapse it works.

[v-csrikanth · Accepted solution]

Hi @Rbakker888 
It looks like there are two main issues causing the refresh failure in your Power BI Semantic Model after modifying the Synapse script and adding calculated columns in Power BI.

1. Check if the External Table Exists in Synapse: Open Azure Studio and run
   SELECT TOP 10 * FROM dbo.dim_SourceKalender

2. Check for Object Locking or Permissions Issues
   The error suggests the table might be locked by another process.
   Try refreshing synapse metadata by running: DBCC FREEPROCCACHE
   Checking if another process is using it: 
   SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('YourDatabase')
3. Convert a Calculated Column to a Measure
   Remove the calculated column and replace it with a measure using DAX.
     If your calculated column was: EG: NewColumn = 'Sales'[Amount] * 1.2
     Convert it into a measure instead: NewMeasure = SUM('Sales'[Amount]) * 1.2
   Update all visuals that reference the column to use the new measure.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[Anonymous · Accepted solution]

Open the pbix file, look at the Steps (or M code) for the query/table and see if any steps are referencing those columns. Sometimes there is a step that removes columns and that is usually where this issues arises.

[Rbakker888]

I did find this removed collumns inside the query, I then removed those steps but when I try refresh it now it comes with the following error. 
DataSource.Error: Microsoft SQL: External table 'dbo.dim_SourceKalender' is not accessible because location does not exist or it is used by another process.
Could not use view or function 'dbo.fact_Orders' because of binding errors.
Details:
DataSourceKind=SQL
DataSourcePath= Azure Server
Message=External table 'dbo.dim_SourceKalender' is not accessible because location does not exist or it is used by another process.
Could not use view or function 'dbo.fact_Orders' because of binding errors.