Creating measures programmatically in Power BI semantic models using C# scripts

In today’s short article, I’ll showcase the usefulness of C# scripts in Tabular Editor by using measure creation as an example. These scripts are incredibly valuable for automating repetitive tasks involved in the creation and adjustment of Power BI semantic models.

Before we dive into the solution, let’s firstly understand the background of our example. We are faced with two challenges:

1. The first challenge is to create six measures derived from a base measure (year ago, difference versus year ago, index versus year ago, prior period, difference versus prior period, and index versus prior period measures) in a least click-consuming manner.

2. The second challenge is to make this measure creation code reusable across multiple semantic models that follow a similar architecture pattern (from the calendar dimension table perspective). We aim to establish a method that can be easily applied to different models, ensuring consistency and simplifying maintenance.

To ensure reusability, we start by defining a few key variables that can be adjusted to the semantic model “needs”. These include the names of the calendar dimension table, the day column, the day year ago column, the year-and-month order column, as well as the format string and format string expressions you wish to use for both the absolute and index measures. These elements will be utilized to create the time transformation measures.

The purpose of the second component of the script is to ensure that we overwrite the measure definitions when adjustments are needed. By solely focusing on measure creation, we would run the risk of having duplicate measures if they already exist. To address this, the idea is to firstly delete the “derived” measures that we will (re)create in the third part of the code.

The third component is dedicated to defining the characteristics of the measures, including their names, expressions, display folders, and format string/format string expressions. Additionally, it focuses on properly formatting the DAX code because if it’s not formatted, it’s not DAX — as Italians say 😉

As you can (hopefully) notice, we are referring to the table and column names that were defined earlier in the script. Furthermore, even within each loop, we have the ability to reference the measures that were defined previously. For example, you can refer to the year ago measure within the definition of the difference versus year ago measure. This flexibility allows for seamless integration and utilization of the previously defined elements throughout the script.

As a side comment and pro tip, I’d like to highlight that when writing DAX formula strings within C# code, you can make use of the “@” symbol to include line breaks. This technique can make your life much easier 🙂

This is how the model looks prior to executing the script.

And this is how it appears after executing the script.

Full code below:

//////////////////////////////////////////////////////////////////
//                                                              //
// Define all format string expressions, table and column names //
//                                                              //
//////////////////////////////////////////////////////////////////

// define dynamic format string expression for absolute measures
string dynamicFormatStringForAbsoluteMeasures = "SELECTEDVALUE ( magnitude[Format], \"#,0\" )";

// define format string for index measures
string formatStringForIndexMeasures = "#,0.0";

// define calendar dimension name
string calendarDim = "calendar_dim";

// define day column name
string dayColumn = "day_num";

// define day year ago column name
string dayYearAgoColumn = "day_num_ya";

//define year month order column name
string YearMonthOrder = "year_month_order";


/////////////////////////////////////////////////////////////////////////////////////////////////////
//                                                                                                 //
// Delete all dependent measures in order not to end with duplicates with ' 1' suffix in the name  //
//                                                                                                 //
/////////////////////////////////////////////////////////////////////////////////////////////////////

var measuresToDelete = new List<Measure>();

foreach (var m in Selected.Measures)
{
    foreach (var am in Model.AllMeasures)
    {
        if (am.Name == m.Name + " YA" || am.Name == m.Name + " IYA" || am.Name == m.Name + " DYA" || am.Name == m.Name + " PP" || am.Name == m.Name + " IPP" || am.Name == m.Name + " DPP")
        {
            measuresToDelete.Add(am);
        }
    }
}

foreach (var m in measuresToDelete)
{
    m.Delete();
}

/////////////////////////////////////////////////////////////
//                                                         //
// Create dependent measures                               //
// Define their names, expression, display folders         //
// Add apporiate Format String Expression or Format String //
// Format DAX expression so that it is more readable       //
//                                                         //
/////////////////////////////////////////////////////////////

foreach(var m in Selected.Measures)
{

    // Year ago measure
    var MeasureYA = m.Table.AddMeasure(
        m.Name + " YA", // Name
  @"
  CALCULATE (
   [" + m.Name + @"],
   REMOVEFILTERS ( " + calendarDim + @" ),
   TREATAS ( DISTINCT ( " + calendarDim + @"[" + dayYearAgoColumn + @"] ), " + calendarDim + @"[" + dayColumn + @"] )
  )", // Expression
        m.DisplayFolder + "\\YA" // Display Folder
    );

    FormatDax(MeasureYA);

    MeasureYA.FormatStringExpression = dynamicFormatStringForAbsoluteMeasures;

    // Index versus year ago measure
    var MeasureIYA = m.Table.AddMeasure(
        m.Name + " IYA", // Name
  "DIVIDE ( [" + m.Name + "], [" + MeasureYA.Name + "] ) * 100", // Expression 
        m.DisplayFolder + "\\YA" // Display Folder
    );

    FormatDax(MeasureIYA);

    MeasureIYA.FormatString = formatStringForIndexMeasures;

    // Difference versus year ago measure
    var MeasureDYA = m.Table.AddMeasure(
        m.Name + " DYA",                    // Name
  "[" + m.Name + "] - [" + MeasureYA.Name + "]", 
        m.DisplayFolder + "\\YA" // Display Folder
    );

    FormatDax(MeasureDYA);

    MeasureDYA.FormatStringExpression = dynamicFormatStringForAbsoluteMeasures;

    // Prior period measure
    var MeasurePP = m.Table.AddMeasure(
        m.Name + " PP", // Name
  @"
  VAR _month_count = COUNTROWS( DISTINCT( '" + calendarDim + @"'[" + YearMonthOrder + @"] ) )
  VAR _month_order_values =
   SELECTCOLUMNS(
    ADDCOLUMNS(
     DISTINCT( '" + calendarDim + @"'[" + YearMonthOrder + @"] ),
     ""@YearMonthOrder"", " + calendarDim + @"[" + YearMonthOrder + @"] + _month_count
    ),
    ""@YearMonthOrder"", [@YearMonthOrder]
   )
  VAR _result =
   CALCULATE(
    [" + m.Name + @"],
    REMOVEFILTERS( '" + calendarDim + @"' ),
    TREATAS( _month_order_values, '" + calendarDim + @"'[" + YearMonthOrder + @"] )
   )
  RETURN
   _result  
  ", // Expression
        m.DisplayFolder + "\\PP" // Display Folder
    );

    FormatDax(MeasurePP);

    MeasurePP.FormatStringExpression = dynamicFormatStringForAbsoluteMeasures;

    // Index versus prior period measure
    var MeasureIPP = m.Table.AddMeasure(
        m.Name + " IPP", // Name
  "DIVIDE ( [" + m.Name + "], [" + MeasurePP.Name + "] ) * 100", // Expression
        m.DisplayFolder + "\\PP" // Display Folder
    );

    FormatDax(MeasureIPP);
 
    MeasureIPP.FormatString = formatStringForIndexMeasures;

    // Difference versus prior period measure
    var MeasureDPP = m.Table.AddMeasure(
        m.Name + " DPP", // Name
  "[" + m.Name + "] - [" + MeasurePP.Name + "]", // Expression
        m.DisplayFolder + "\\PP" // Display Folder
    );

    FormatDax(MeasureDPP);

    MeasureDPP.FormatStringExpression = dynamicFormatStringForAbsoluteMeasures;

}

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass