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:

  • Connect to an instance of Power BI Desktop
  • Iterate through every Table in the model
  • Iterate through every Column in the “current” table from the outer loop
  • If the Column is numeric and not hidden, create a simple [Sum of <column>] measure

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.

  • Line 11 contains the connection string to the model. This connection string will need to be changed to suit your current model.
  • Line 15 iterates through every Table in the model
  • Line 17 iterates through every Column in the outer-loop
  • Line 25 the operator should be && rather than the HTML code shown by the syntax code highlighter
  • Line 29 is the actual DAX expression, so needs to be valid DAX
  • Line 30 defines the name of the display folder for the auto measures
  • Line 39 marks the auto measures with an annotation – which will make removing auto measures easier to perform
  • Line 53 commits the changes to the model (if successful)

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.

4.8 5 votes
Article Rating

Leave a Reply

6 Comments
Inline Feedbacks
View all comments
Prasan
Prasan
3 years ago

Nice one Phil. The same can be achieved using tabular editor advanced scripting features. //all columns in the model foreach(var column in Model.Tables.SelectMany(t => t.Columns)) { //all numeric columns if( ( column.DataType == DataType.Int64 || column.DataType == DataType.Decimal || column.DataType == DataType.Double ) && column.IsHidden==false) { //setting measure name, expression and display folder var newMeasure = column.Table.AddMeasure( “#” + column.Name, // Name “SUM(” + column.DaxObjectFullName + “)”, // DAX expression column.DisplayFolder // Display Folder ); // Set the format string on the new measure: newMeasure.FormatString = “0.00”; // Provide some documentation: newMeasure.Description = “This measure is the sum of column… Read more »

Didier Terrien
3 years ago

That’s great !!! Thank you Phil.
I cannot wait for the next post ! I suppose you will show how to get the port number.
It would be great to implement a menu for functions selection.

Andy Clapham
3 years ago

That’s triggered my imagination, thanks! As a dev who’s inherited what could possibly be the world’s most complicated (and tbh blooming’ good for business) pbix, I’m desperate to get what I can out of “no-code” chaos and under vcs control. This could be a better route than trying to hack on the files directly.
I’d be interested if you or any of your readers know of anybody working on anything similar.

trackback

[…] Phil’s blog for follow-ups on this, starting with Automatically create measures in Power BI using VS Code (C# code […]