Phil Seamark on DAX

Automatically create measures in Power BI using VS Code

My last blog introduced the idea of using Microsoft Visual Studio Code to work with Power BI Models. For this article, I build on that idea by showing how you can use a TOM based script to automatically generate measures in your model Power BI (or Azure Analysis Services) model.

For simplicity, the example in this blog will do the following:

The exercise

Create a new folder somewhere on your machine and open the folder using VS Code. I will assume you have performed the one-off tasks mentioned in Step 1 and Step 2 from my previous blog to 1) install visual studio and 2) install the .Net Core SDK.

Open the newly created folder in VS Code and run the following two commands from a terminal window. These get explained as steps 3 & 4 in my previous blog.

dotnet new console
dotnet add package Microsoft.AnalysisServices.NetCore.retail.amd64 --version 19.6.0-Preview

These two commands create the shell project that we can add our TOM script.

Finally, add the following C# code into the Program.cs file.

using System;
using Microsoft.AnalysisServices.Tabular;

namespace AutoMeasure
{
    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server();
            server.Connect("localhost:54627");

            Model model = server.Databases[0].Model;

            foreach(Table table in model.Tables)
            {
                foreach(Column column in table.Columns)
                {
                    if(
                            (
                            column.DataType == DataType.Int64 ||
                            column.DataType == DataType.Decimal || 
                            column.DataType == DataType.Double 
                            )
                            && column.IsHidden==false)
                    {
                    
                        string measureName = $"Sum of {column.Name} ({table.Name})";
                        string expression = $"SUM('{table.Name}'[{column.Name}])";
                        string displayFolder = "Auto Measures";

                        Measure measure = new Measure()
                        {
                            Name = measureName ,
                            Expression = expression,
                            DisplayFolder = displayFolder
                        };
                        
                        measure.Annotations.Add(new Annotation(){Value="This is an Auto Measure"});

                        if(!table.Measures.ContainsName(measureName))
                        {
                            table.Measures.Add(measure);
                        }
                        else
                        {
                            table.Measures[measureName].Expression = expression;
                            table.Measures[measureName].DisplayFolder = displayFolder;
                        }
                    }
                }
            }
            model.SaveChanges();
        }
    }
}

Notes for the code:

Double click inside the code to enable select-all, copy/paste etc.

After running the code in Visual Studio (hit the F5 key), you can jump across to check your model (in my case Power BI Desktop) to see if the new measures appear.

As you can see, my Sales table now has a folder called Auto Measures, and this now has three new measures that can be used in reports.

Easy as that!

Summary

The next steps are to compile the app and add to the new External Tools area in the Power BI Desktop ribbon. Once this gets added, you can hit the button to run the script automatically over your model to perform common bulk tasks with ease. I will show how to do this in my next blog.

Additional measures, based around other common patterns (MAX, MIN, AVERAGE) etc. should be easy enough to add.

Another idea (to be covered in a future blog) is to skip columns involved in relationships – as these columns probably don’t need auto measures.

As always, let me know how you get on. I enjoy hearing how people get on using tools like this in interesting ways.