Local Functions in Advanced Scripting with Tabular Editor

When I implemented the C# script for my last post I had to replicate some code, since advanced scripting in Tabular Editor as is doesn’t provide a way to define functions.

But starting from C# version 7.0 it’s possible to declare local functions, and both Tabular Editor versions, the free and the commercial ones, allow to specify the C# compiler to be used and the language version.

Therefore I decided to see if local functions could be used with advanced scripting.

They do, and I was able to remove the duplicate code from my last C# script.

Following the Advanced Scripting documentation on the Tabular Editor site I set the compiler path to my current installation of Visual Studio 2019.

I configured the Roslyn compiler for Tabular Editor 2, the free version

And I also configured it for Tabular Editor 3, the commercial version

The Roslyn compiler path on my PC is
C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\Roslyn

It might be different on other installations

The language version I specified is the 8.0, using the corresponding command line switch for the compiler
-langversion:8.0

With this version of the compiler we can use local functions that are private methods defined inside another method and can be called only from the method that contains it. This allows us to declare functions, since the script we write is wrapped by Tabular Editor inside a method.

For instance, opening a new script we can implement a function that adds two numbers and calls it (here I’m using TE3, but it also works in TE2)

We write the simple script that follows and we run it using the Run Script button

int add(int a, int b)
{
    return a + b;
}

int c = add(2, 3);

Output(c);

The Output() method shows its argument inside a popup window

And that’s it, once I verified that the local functions worked, I changed the C# script that generates the Parent-Child hierarchy of my last post adding the “local functions” section and replacing the duplicate code using functions instead.

// configuration start

int levels = 4;
int previouslevels = 4;

string tableName = "Entity";
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

// local functions

void DeleteHierarchy(string hierarchyName, string tableName)
{
    var table = Model.Tables[tableName];
    var hierarchiesCollection = table.Hierarchies.Where( 
        m => m.Name == hierarchyName );
    if (hierarchiesCollection.Count() > 0)
    {
        hierarchiesCollection.First().Delete();
    }
}

void DeleteMeasure(string measureName, string tableName)
{
    var table = Model.Tables[tableName];
    var measuresCollection = table.Measures.Where( 
        m => m.Name == measureName ); 
    if (measuresCollection.Count() > 0)
    {
        measuresCollection.First().Delete();
    }
} 


void DeleteCalculatedColumn(string calculatedColumnName, string tableName)
{
    var table = Model.Tables[tableName];
    var calculatedColumnsCollection = table.CalculatedColumns.Where( 
        m => m.Name == calculatedColumnName );
    if (calculatedColumnsCollection.Count() > 0)
    {   
        calculatedColumnsCollection.First().Delete();
    }
}    

// cleanup
DeleteHierarchy(hierarchyName, tableName);
foreach (var wrapperMeasurePair in measuresToWrap)
{
    DeleteMeasure(wrapperMeasurePair.Value, wrapperMeasuresTableName);
} 
DeleteMeasure(browseDepthMeasureName, tableName);
DeleteMeasure(rowDepthMeasureName, tableName);
DeleteCalculatedColumn(depthName, tableName);
for (int i = 1; i <= previouslevels; ++i)
{
    string levelName = string.Format(levelNameFormat, i);
    DeleteCalculatedColumn(levelName, tableName);
}
DeleteCalculatedColumn(pathName, tableName);

// create calculated columns
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
";

string daxPath = string.Format( "PATH({0}[{1}], {0}[{2}])", 
    tableName, keyName, parentKeyName);
var table = Model.Tables[tableName];
table.AddCalculatedColumn(pathName, daxPath);

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);

This script and the Power BI pbix files can be found in my github shared project