Issue with Calculated Column Referencing Another Table Created using REST APIs

[akash738]

I'm encountering an issue while adding a calculated column to a calculated table (TestCalculatedTable) within a Power BI dataset. The dataset contains two physical tables: Inventory and Transactions, and I'm trying to create a calculated column(StockStatus) in the calculated table(TestCalculatedTable) that references columns from the Inventory table.

To add a calculated column (StockStatus) to the calculated table (TestCalculatedTable) with the following DAX expression:

IF(
    'Inventory'[Quantity in Stock] < 'Inventory'[Reorder Level],
    "Below Reorder Level",
    "Sufficient Stock"
)

I'm using the following payload:

{"tableName": "TestCalculatedTable","calculatedColumnExpression": "IF(\n        'Inventory'[Quantity in Stock] < 'Inventory'[Reorder Level],\n        \"Below Reorder Level\",\n        \"Sufficient Stock\"\n    )","calculatedColumnName": "StockStatus" } 

 

Observations Across Scenarios:

1. Without Any Relationship between TestCalculatedTable and Inventory:
   • ❌ Error: The query referenced calculated column 'TestCalculatedTable'[StockStatus] which does not hold any data because there is an error in its expression.
   • Root Cause: The column references another table without a relationship or context binding.
2. Without Relationship But Using RELATED:
   • Updated DAX: IF(
         RELATED('Inventory'[Quantity in Stock]) < RELATED('Inventory'[Reorder Level]),
         "Below Reorder Level",
         "Sufficient Stock"
     )
      
   • ❌ Same Error persists: The query referenced calculated column 'TestCalculatedTable'[StockStatus] which does not hold any data because there is an error in its expression.
      
   • Root Cause: The RELATED() function cannot resolve the relationship if none exists.
3. With Relationship + Using RELATED:
   • A relationship was created between TestCalculatedTable and Inventory
   • After adding the relationship and refreshing the model using:
     model.RequestRefresh(Tabular.RefreshType.Calculate);
      
   • ✅ Success: The column StockStatus is correctly created with the expected values.

Note:
Interestingly, this error only appears in the Power BI Service/embedded report, while the same expression works without error in our backend solution using the Execute(Test Query) method.



to Add a calculated column I'm using this code:

/// <summary>
/// Adds a new calculated column to the specified table in the Power BI dataset.
/// </summary>
/// <param name="calculatedColumnModel">The model containing the calculated column's properties.</param>
/// <returns>True if the calculated column was successfully added; otherwise, false.</returns>
public async Task AddCalculatedColumn(CalculatedColumnModel calculatedColumnModel, string datasetId, string workspaceId)
{
    Tabular.Server? server = null;
    try
    {
        (Tabular.Model model, Tabular.Server serverInstance) = await _utilityService.GetModelAsync(workspaceId, datasetId);
        server = serverInstance;

        var table = model.Tables.Find(calculatedColumnModel.TableName);

        if (table == null)
            throw new DirectoryNotFoundException(string.Format(MessageConstants.TableNotFoundMessage, calculatedColumnModel.TableName));


        // Check if the calculated column already exists
        var existingColumn = table.Columns.OfType<CalculatedColumn>().FirstOrDefault(c => c.Name == calculatedColumnModel.CalculatedColumnName);

        if (existingColumn != null)
        {
            throw new InvalidOperationException(string.Format(MessageConstants.CalculatedColumnAlreadyExistsMessage, calculatedColumnModel.CalculatedColumnName));
        }
        else
        {
            // Create a new calculated column and add it to the table
            var newColumn = new CalculatedColumn
            {
                Name = calculatedColumnModel.CalculatedColumnName,
                Expression = calculatedColumnModel.CalculatedColumnExpression
            };
            table.Columns.Add(newColumn);
        }

        model.SaveChanges();
        model.RequestRefresh(Tabular.RefreshType.Calculate);
        model.SaveChanges();
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error adding calculated column");
        throw new InvalidOperationException(ex.Message);
    }
    finally
    {
        SafeDisconnectServer(server);
    }
}

[Poojara_D12]

Hi @akash738 

You're facing a nuanced issue when programmatically adding a calculated column to a calculated table (TestCalculatedTable) in Power BI using the Tabular Object Model (TOM). Specifically, the DAX expression for the new column (StockStatus) references another table (Inventory), which leads to errors in the Power BI Service unless a relationship exists between the two tables. Your observations correctly identify the root cause: calculated columns cannot reference columns from unrelated tables unless there's a defined relationship in the model. Using RELATED() without a relationship also fails, as RELATED() relies on an existing relationship to fetch values from the related table. While this works fine when you query the model using Execute(Test Query)—because it executes in a flexible evaluation context—Power BI Service enforces stricter semantic rules during dataset refresh and report rendering. The solution that worked was to explicitly create a relationship between TestCalculatedTable and Inventory before adding the calculated column, which then allowed RELATED() to work correctly and populate values as expected. Your AddCalculatedColumn() method appears well-structured and follows best practices by validating the column's existence, adding it if not present, and calling RequestRefresh(Tabular.RefreshType.Calculate). However, the key insight is that Power BI requires a relationship between tables for cross-table column references in calculated columns, especially in production scenarios like Service or Embedded reports. Without that relationship, even valid-looking DAX fails because of the evaluation context limitations.

[Akash_Varuna]

Hi @akash738 It is because calculated columns in Power BI require a relationship or proper context to reference another table. Without a relationship, functions like RELATED cannot resolve the dependency, leading to errors. Ensure a relationship exists between TestCalculatedTable and Inventory before creating the calculated column. Update your code to check and create relationships programmatically if missing, and refresh the model to apply the changes, ensuring the calculated column operates correctly in all environments.

[v-pgoloju]

Hi @akash738,

Thanks for posting your question in the Microsoft Fabric Forum Community.

To make your DAX expression work, the best way is to create a proper relationship between your calculated table (TestCalculatedTable) and the Inventory table before adding the new column. Without this relationship, Power BI can't understand how to pull data from the Inventory table into the calculated column, which causes the error.

Thanks & regards,
Prasanna Kumar