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

2 Comments

  1. Nice post, thanks for sharing!

    Btw. Tabular Editor 3 allows you to define types inside scripts, even without the Roslyn compiler. This functionality will also be coming to Tabular Editor 2.x. This allows syntax such as the following:

    class MyClass
    {
    public void SayHello()
    {
    Info(“Hello world!”);
    }
    }

    var myClassInstance = new MyClass();
    myClassInstance.SayHello();

    Like

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s