The best way to generate data driven Measures in Power BI using Tabular Editor

This is a follow-up to my previous post on using a combination of DAX Studio and Tabular Editor to generate a series of measures based on the values in a given column. Which in turn was inspired by this excellent post by Phil Seamark.

While I was writing my previous post I sent a question to Daniel who is the author of Tabular Editor, asking if there was a way of getting the connection details when Tabular Editor was launched from the External Tools. The idea was that I should be able to do everything Phil did in his original script from the Tabular Editor Advanced Scripting feature.

Within an hour he actually came back with an even better approach which he had just added to Tabular Editor. As of Tabular Editor 2.12.0 the database object in Tabular Editor now has 3 new helper methods.

• void ExecuteCommand(string tmsl)
• IDataReader ExecuteReader(string dax)
• DataTable ExecuteDax(string dax)
• DataSet ExecuteDax(string dax)
• object EvaluateDax(string dax)

Update 30 Aug 2020 There was an issue with certain queries and the ExecuteDax method. This has now been fixed and Daniel has extended this method to return a DataSet (which can contain multiple DataTables) and he has also added the EvaluateDax method which can evaluate simple DAX expressions.

Also these methods are also exposed as static methods. What this means is that you can just call ExecuteReader(query) instead of Model.Database.ExecuteReader(query)

These let you run DAX or TSML scripts directly against a connected data model. The advantage of these methods is that you don’t need to do any searching for port numbers or loading additional libraries and they make use of the existing authenticated connection, so there is no need to re-authenticate to the data model.

So with a simple 14 line script and no additional tools or downloads you can generate a set of data driven measures for your model. Simply click run (1) and your new measures appear in your model (2) and once you save them they will appear in Power BI Desktop.

Below is the text version of the advanced script if you want to modify this to work in your environment. This script is just a tweaked copy of the main loop in the original one Phil posted with a few small syntax changes as the Advanced Scripting appears to use a slightly older version of the c# compiler which does not have some of the newer features like interpolated strings.

string query = "EVALUATE VALUES('Product'[Color])";

using (var reader = Model.Database.ExecuteReader(query))
{
    // Create a loop for every row in the resultset
    while(reader.Read())
    {
        string myColour = reader.GetValue(0).ToString();
        string measureName = "Sum of " + myColour + " Sales Amount";
        string myExpression = "CALCULATE( SUM('Sales'[Sales Amount]), 'Product'[Color] = \""  + myColour + "\")";
                            
        Model.Tables["Sales"].AddMeasure(measureName, myExpression, "AutoMeasures");
    }
}

If you are interested in more information on what can be done with the Advanced Scripting feature you can check out the documentation and the useful script snippets page. While you are there make sure to have a look at the Custom Actions as these are a great way to trigger generic scripts that you use on a regular basis.

There is also a public github scripts repository with example scripts which you can download and use.