This article will discuss about the ability to add a dynamic ColumnId to a calculated table into a tabular model.

In order to do add the dynamic ColumnId we will use a DAX expression. For the purpose of this article, we will use a simple model. This simple model contains a table called DimProduct:

First, we add a new calculated table in our existing model. This calculated table gets the existing value from the column, DimProduct[ColorName]. For that, we write the DAX expression to insert our new calculated table into our model:

=UNION(
    ALL(DimProduct[ColorName]);
    DATATABLE(
    "Color"; STRING;
    {
        {"*custom*"};
        {"Cyan"};
        {"Magenta"};
        {"Lime"};
        {"Maroon"}
    }
   )
)

This DAX expression gets all the distinct value from DimProduct[ColorName] and adds new custom values. As you can see, in the resulting data, all the value from "*custom*" are not present in the existing DimProduct Table:

Secondly, we have to get the ColorId corresponding to the ColorName in the existing DimProduct table. To do this, we add a new column ColordId2 by using a DAX expression again. This new column will be our intermediate column to calculate our dynamic ColumnId:

=VALUE(LOOKUPVALUE(
    DimProduct[ColorID];
    DimProduct[ColorName];
    DimColor[ColorName])
)

As we can see, we have all the id values corresponding to the DimProduct[ColorId], except those new values from our calculated table DimColor.

Third, we write a DAX expression to complete the sequence of id automatically if the value of ColorId2 is blank :

=IF(
    ISBLANK(DimColor[ColorId2]);
    MAX(DimColor[ColorId2]) + RANKX( FILTER(DimColor; ISBLANK(DimColor[ColorId2])); DimColor[ColorName]; DimColor[ColorName]; ASC; Skip);
    DimColor[ColorId2]
)

Now, all the missing id values are completed by the DAX expression.

This technique is useful by adding new value from existing table while keeping existing id values.