Creating hundreds of measures, one by one in the SSAS Tabular Model is extremely time-consuming. It does not make a difference, if we have measures which follow a similar naming convention and formula or have similar base columns. If we create the measures manually, then each time we will have to write it, or copy-paste-modify and validate them every time we process it with the enter key.

In case of an excel there are various tricks and formulas to generate the formula names and similar formula quickly. With this, we can create measures quickly and make the reference document ready to be delivered.

From this article, we will learn how to quickly upload the list of measures from an excel sheet to the 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 the System – one may have to override any security warnings by Windows defender about the publisher being “unknown”. We then create a shortcut to the TabularEditor.exe file and pin it to Start for easy access.

After installation, run Tabular Editor. It would like this:

Solution Steps:

1. Create an excel sheet with the list of measures to be created in the 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

– Create an excel sheet in the below format:

– Once the list of measures is ready, we need to save it as tab delimited text file in the ‘C:\temp\measures.txt’ location.

Run the Script

– Open the model in the Visual Studio SSDT.

– We need to make sure to have the measures.txt file at the “C:\temp\” location.

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

– Go to the Advanced Scripting pane. Copy the code given 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 separatorvar

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

– Click on Run Script

That is how measures are created in the Tabular Model in a matter of seconds.

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

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