Parent-Child Hierarchy Scripting with Tabular Editor

Implementing Parent-Child hierarchies in DAX requires the usage of a special set of functions, PATH, PATHITEM and PATHLENGTH, specifically designed for this purpose. These functions are used to flatten the hierarchy as as set of calculated columns. This means that the depth of the hierarchy is fixed and limited to the number of calculated columns that was decided at design time.

When the depth increases, new calculated columns must be added. This, together with the fact that I quickly forget how to use the PATH* functions, since I use them only when dealing with parent-child hierarchies, gave me the idea to write a script to automatically generate parent-child hierarchies.

As a test project I decided to implement a script to re-create the basic parent-child DAX pattern described in the Parent-child hierarchies DAX pattern

My conclusion is that this kind of script can be written and works smoothly, but it requires to enable the unsupported features of Power BI in settings of Tabular Editor.

Since I wrote the script using variables to contain the names in the model, it should be easily adapted to other models too, but I still have to try it.

This is the report built using the script generated parent-child hierarchy

Loading and Executing The Script

As a start point I used a small model containing only three tables, that I saved as the “Parent-Child Hierarchy Scripting with Tabular Editor before.pbix” file.

Then to ran Tabular Editor 2 we click on its icon from the External Tools ribbon of Power BI

In Tabular Editor we can see the Entity table only has four columns

Then we have to enable the Unsupported Power BI features in the Preferences (through the File->Preferences… menu)

This is necessary, otherwise an attempt to create a calculated column generates the following error

The Advanced Scripting tab shows the editor where the script can be written, and the “load”, “save” and “run” buttons

By clicking on the “Load” button and selecting the “Parent-Child Hierarchy.cs” file the script appears in the edit window and can be executed

The result is that the calculated columns and the measures of the pattern are generated. Finally we must click on the button to save the changes to the connected Power BI model.

After saving the model to Power BI, we must click on the “Refresh Now” button to answer the Power BI request to manually refresh the model

Finally we can find the generated calculated columns, measures and hierarchy in the model

The first part of the script contains a configuration section with a list of variables that can be used to adapt the script to other models. The first one is the “levels” variables, that contains the number of “level” columns to be generated, that corresponds to the maximum depth allowed for the hierarchy.

Here follows the entire script, that just follows the pattern description step by step, with the only difference that I added an additional measure [# categories base], that is just for testing purpose.

// configuration start

int levels = 4;

string tableName = "Entity";
var table = Model.Tables[tableName];

string pathName = "EntityPath";
string keyName = "EntityKey";
string nameName = "EntityName";
string parentKeyName = "ParentEntityKey";
string levelNameFormat = "Level{0}";
string depthName = "Depth";
string rowDepthMeasureName = "EntityRowDepth";
string browseDepthMeasureName = "EntityBrowseDepth";
string wrapperMeasuresTableName = "StrategyPlan";
string hierarchyName = "Entities";
var wrapperMeasuresTable = Model.Tables[wrapperMeasuresTableName];
SortedDictionary<string, string> measuresToWrap = 
    new SortedDictionary<string, string> 
{ 
    { "# Categories", "# Categories Base"}, 
    { "Sum Amount", "Total Base" } 
};
// configuration end

string daxPath = string.Format( "PATH({0}[{1}], {0}[{2}])", 
    tableName, keyName, parentKeyName);
    

// cleanup

var hierarchiesCollection = table.Hierarchies.Where( 
    m => m.Name == hierarchyName );
if (hierarchiesCollection.Count() > 0)
{
    hierarchiesCollection.First().Delete();
}


foreach (var wrapperMeasurePair in measuresToWrap)
{
    var wrapperMeasuresCollection = wrapperMeasuresTable.Measures.Where( 
        m => m.Name == wrapperMeasurePair.Value ); 
    if (wrapperMeasuresCollection.Count() > 0)
    {
        wrapperMeasuresCollection.First().Delete();
    }
} 

var browseDepthMeasureCollection = table.Measures.Where( 
    m => m.Name == browseDepthMeasureName );
if (browseDepthMeasureCollection.Count() > 0)
{
    browseDepthMeasureCollection.First().Delete();
}

var rowDepthMeasureCollection = table.Measures.Where( 
    m => m.Name == rowDepthMeasureName );
if (rowDepthMeasureCollection.Count() > 0)
{
    rowDepthMeasureCollection.First().Delete();
}

var depthCollection = table.CalculatedColumns.Where( 
    m => m.Name == depthName );
if (depthCollection.Count() > 0)
{   
    depthCollection.First().Delete();
}

for (int i = 1; i <= levels; ++i)
{
    string levelName = string.Format(levelNameFormat, i);
    var levelCalculatedColumnCollection = 
        table.CalculatedColumns.Where( m => m.Name == levelName );
    if (levelCalculatedColumnCollection.Count() > 0)
    {    
        levelCalculatedColumnCollection.First().Delete();
    }
}

var pathCalculatedColumnCollection = table.CalculatedColumns.Where( 
    m => m.Name == pathName );
if (pathCalculatedColumnCollection.Count() > 0)
{
    pathCalculatedColumnCollection.First().Delete();
}

// create calculated columns
table.AddCalculatedColumn(pathName, daxPath);

string daxLevelFormat = 
@"VAR LevelNumber = {0}
VAR LevelKey = PATHITEM( {1}[{2}], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE( {1}[{3}], {1}[{4}], LevelKey )
VAR Result = LevelName
RETURN
    Result
";

for (int i = 1; i <= levels; ++i)
{
    string levelName = string.Format(levelNameFormat, i);
    string daxLevel = string.Format(daxLevelFormat, i, 
        tableName, pathName, nameName, keyName);
    table.AddCalculatedColumn(levelName, daxLevel);
}

string daxDepthFormat = "PATHLENGTH( {0}[{1}] )";
string daxDepth = string.Format(
    daxDepthFormat, tableName, pathName); 
table.AddCalculatedColumn(depthName, daxDepth);


// Create Hierarchy

table.AddHierarchy(hierarchyName);
for (int i = 1; i <= levels; ++i)
{
    string levelName = string.Format(levelNameFormat, i);
    string daxLevel = string.Format(daxLevelFormat, i, 
        tableName, pathName, nameName, keyName);
    table.Hierarchies[hierarchyName].AddLevel(levelName);
}

// Create measures
string daxRowDepthMeasureFormat = "MAX( {0}[{1}])";
string daxRowDepthMeasure = string.Format(
    daxRowDepthMeasureFormat, tableName, depthName );
table.AddMeasure(rowDepthMeasureName, daxRowDepthMeasure);

string daxBrowseDepthMeasure = "";
for (int i = 1; i <= levels; ++i)
{
    string levelMeasureFormat = "ISINSCOPE( {0}[{1}] )";
    string levelName = string.Format(levelNameFormat, i);
    daxBrowseDepthMeasure += string.Format(
        levelMeasureFormat, tableName, levelName);
    if (i < levels)
    {
        daxBrowseDepthMeasure += " + ";
    }
}
table.AddMeasure(browseDepthMeasureName, daxBrowseDepthMeasure);

string daxWrapperMeasureFormat = 
@"VAR Val = [{0}]
VAR ShowRow = [{1}] <= [{2}]
VAR Result = IF( ShowRow, Val )
RETURN
    Result
";

foreach (var wrapperMeasurePair in measuresToWrap)
{
    string daxWrapperMeasure = string.Format(daxWrapperMeasureFormat,
        wrapperMeasurePair.Key, // measure to be wrapped
        browseDepthMeasureName,
        rowDepthMeasureName);
    wrapperMeasuresTable.AddMeasure(wrapperMeasurePair.Value, daxWrapperMeasure);
    
} 

table.Measures.FormatDax(false);
wrapperMeasuresTable.Measures.FormatDax(false);

The documentation about the Advanced Scripting can be found on the Tabular Editor site,