SQLServerCentral Article

Adding a dynamic ColumnId to a DAX Calculated Table

,

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.

 

Rate

4.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (2)

You rated this post out of 5. Change rating