Forum Discussion
Unable to Delete Columns or Measures from Calculated Table via XMLA Endpoint – No Errors, No Effect
Hi all,
I'm currently working with the XMLA endpoint to manage Power BI datasets via my custom API. Here's the issue I'm facing:
What works:
I can successfully create a calculated table using a DAX expression.
The calculated table appears correctly in Power BI and shows up after refresh.
I can delete entire calculated tables using the same API.
I can delete columns and measures from regular (non-calculated) tables without any issues using same api.
What doesn't work:
- When I try to delete a column or measure from a calculated table, it does not get deleted.
- No errors are thrown, and model.SaveChanges() executes without exception.
- The column/measure simply remains in the model after the operation.
Sample Code Used for Deletion:
var column = table.Columns.FirstOrDefault(c => c.Name == deleteColumnModel.ColumnName);
if (column != null)
{
table.Columns.Remove(column);
model.SaveChanges();
model.RequestRefresh(Tabular.RefreshType.Calculate);
model.SaveChanges();
}- Key Notes:
I'm connecting via XMLA endpoint using TOM.
This issue is isolated to calculated tables only.
- Question:
Is this a known limitation when working with calculated tables via XMLA?
Are there any workarounds or recommended practices to remove individual columns or measures from a calculated table programmatically?
I'd appreciate any guidance or suggestions. Thanks in advance!
Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
Yes this is a known limitation. Calculated tables are defined entirely by their DAX expression and their structure is regenerated on every refresh. This means deleting a column or measure directly, like table.Columns.Remove(column) won’t work. Even though model.SaveChanges() completes without errors, the schema is restored from the DAX during RefreshType.Calculate. To remove a column or measure from a calculated table programmatically, you need to modify the table's DAX expression to exclude it. Example:
var table = model.Tables["YourCalculatedTable"] as Microsoft.AnalysisServices.Tabular.CalculatedTable;
if (table != null)
{
string originalDax = table.Expression;
string updatedDax = ModifyDaxExpression(originalDax, "ColumnToRemove"); // You implement this
table.Expression = updatedDax;
model.SaveChanges();
model.RequestRefresh(Microsoft.AnalysisServices.Tabular.RefreshType.Calculate);
model.SaveChanges();
}
If modifying the expression is too complex, the fallback is to delete and recreate the table with a new expression. Example:
model.Tables.Remove(model.Tables["YourCalculatedTable"]);
model.SaveChanges();
var newTable = new Microsoft.AnalysisServices.Tabular.CalculatedTable
{
Name = "YourCalculatedTable",
Expression = "SELECTCOLUMNS(Table1, \"Col1\", Table1[Col1])" // Updated DAX
};
model.Tables.Add(newTable);
model.SaveChanges();
model.RequestRefresh(Microsoft.AnalysisServices.Tabular.RefreshType.Calculate);
model.SaveChanges();
This behavior is expected, not a bug. To change the structure of a calculated table, always update its Expression. Validate the DAX beforehand using tools like DAX Studio and check for dependencies before modifying or recreating the table to avoid breaking reports.
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
4 Replies
v-hashadapuCommunity Support
Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
Yes this is a known limitation. Calculated tables are defined entirely by their DAX expression and their structure is regenerated on every refresh. This means deleting a column or measure directly, like table.Columns.Remove(column) won’t work. Even though model.SaveChanges() completes without errors, the schema is restored from the DAX during RefreshType.Calculate. To remove a column or measure from a calculated table programmatically, you need to modify the table's DAX expression to exclude it. Example:
var table = model.Tables["YourCalculatedTable"] as Microsoft.AnalysisServices.Tabular.CalculatedTable;
if (table != null)
{
string originalDax = table.Expression;
string updatedDax = ModifyDaxExpression(originalDax, "ColumnToRemove"); // You implement this
table.Expression = updatedDax;
model.SaveChanges();
model.RequestRefresh(Microsoft.AnalysisServices.Tabular.RefreshType.Calculate);
model.SaveChanges();
}
If modifying the expression is too complex, the fallback is to delete and recreate the table with a new expression. Example:
model.Tables.Remove(model.Tables["YourCalculatedTable"]);
model.SaveChanges();
var newTable = new Microsoft.AnalysisServices.Tabular.CalculatedTable
{
Name = "YourCalculatedTable",
Expression = "SELECTCOLUMNS(Table1, \"Col1\", Table1[Col1])" // Updated DAX
};
model.Tables.Add(newTable);
model.SaveChanges();
model.RequestRefresh(Microsoft.AnalysisServices.Tabular.RefreshType.Calculate);
model.SaveChanges();
This behavior is expected, not a bug. To change the structure of a calculated table, always update its Expression. Validate the DAX beforehand using tools like DAX Studio and check for dependencies before modifying or recreating the table to avoid breaking reports.
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.- v-hashadapu
Hi Anonymous ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions! - v-hashadapu
Hi Anonymous ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you! - v-hashadapu
Hi Anonymous , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!