Automatically Generate Measures in SSAS Tabular Model using Tabular Editor

Introduction

Creating 100’s of measures one by one in the SSAS Tabular Model is time-consuming. It does not matter if you have measures which follow similar naming conventions and formula or make use of similar base columns. If you are doing it manually each time you will have to write it, or copy-paste-modify and validate each time you press enter.

In excel you can use various tricks and formulas to generate the formula names and similar formulae quickly. With that, you can not only create measures quickly but make the reference document ready to be delivered.

From this article, you will learn a trick how you can quickly upload the list of measures from an excel sheet to your Tabular Model.

Download and Install Tabular Editor — Prerequisite

Firstly, download the latest version of the Tabular Editor from GitHub here https://tabulareditor.github.io/

Install the tool on your PC — you may have to override any security warnings by Windows defender about the publisher being “unknown”. You can then create a shortcut to the TabularEditor.exe file and pin it to Start for easy access. Make sure you leave the option to add a shortcut checked during installation.

After installation, run Tabular Editor and look. It looks like this.

Solution Steps

This solution is in two steps:

1. Create an excel sheet with the list of measures you want to create in your Tabular Model.

2. Run the CS Script in the tabular Editor to read the file and generate the measures in the Tabular Model.

Excel Template

1. Create an excel sheet in the below format.

2. Once the list of measures is ready you can save it as tab delimited text file in the ‘C:\temp\measures.txt’ location.

Run the Script

1. Open the model in Visual Studio SSDT.

2. Make sure you have the measures.txt file at the “C:\temp\” location.

3. Open the model.bim in tabular editor. Right Click model.bim -> Click on Open With -> Tabular Editor

4. Go to the Advanced Scripting pane. Copy the code given in below and paste it in the Advanced Scripting pane

var measureMetadata = ReadFile(@"C:\temp\measures.txt");
// Split the file into rows by CR and LF characters:
var tsvRows = measureMetadata.Split(new[] {'\r','\n'},StringSplitOptions.RemoveEmptyEntries);
// Loop through all rows but skip the first one:
foreach(var row in tsvRows.Skip(1))
{
var tsvColumns = row.Split('\t');     // Assume file uses tabs as column separator
var tableName = tsvColumns[0];                // table name
var MeasureName = tsvColumns[1];
var MeasureDAX = tsvColumns[2].Trim('"').Replace("\"\"","\"");
var MeasureFormatString = tsvColumns[3].Trim('"').Replace("\"\"","\"");
var MeasureDisplayFolder = tsvColumns[4].Trim('"').Replace("\"\"","\"");
// delete measure if already there
if (Model.Tables[tableName].Measures.Contains(MeasureName)) {
Model.Tables[tableName].Measures[MeasureName].Delete();
}
var measure = Model.Tables[tableName].AddMeasure(MeasureName);
measure.FormatString = MeasureFormatString;
measure.Expression = MeasureDAX;
measure.DisplayFolder = MeasureDisplayFolder;
measure.SetAnnotation("AUTOGEN", "1");
// Set a special annotation on the measure, so we can find it and delete it the next time the script is executed.
measure.SetAnnotation("AutoMeasures", "1");
}

5. Click on Run Script

That is, it! Measures are created in the Tabular Model in a matter of seconds.

Here is a great link to some useful scripts https://github.com/otykier/TabularEditor/wiki/Useful-script-snippets.

You can do a lot of things with Tabular Editor, including creating measures in Power BI templates, migrating the measures to different models, or automating similar actions. Here is the link to documentation and blogs for tabular editor: https://tabulareditor.com/