Creating measures programmatically in Power BI semantic models using C# scripts (Part 2)

This short article is a continuation or expansion of the previous one (link here). Imagine a situation where you have already created and tested all variations of one measure, and now you want to replicate the same pattern for other measures. However, these new measures need to be built on different tables, columns, and component measures compared to the original ones. In such a scenario, it shouldn’t come as a surprise that C# scripts can once again be extremely helpful in speeding up the process of creating your semantic model.

The code provided below takes into account several inputs required from your end, such as the current and new measure names, current and new tables, columns, and nested measures, as well as the current and new display folder names. It may take up to a minute for you to provide this information, but the time you save later will be well worth it. Trust me, the small investment of a minute now will result in significant time savings down the line 😉

Once the necessary information is provided, the code will generate new measures for each selected measure. It will replace essential elements like measure name, expression, and display folder. Additionally, you can ensure that the new measures are appropriately formatted and inherit properties from the original measures, such as Format Strings (Expressions). This automated process saves you from the manual task of creating each measure and guarantees consistency throughout the new measures.

// define measure name replacement
var oldStringName = "POS Unit Sales (Mth)";
var newStringName = "POS Unit Sales (Bdgt)";

// define measure expression replacements
var replacementPairsExpression = new Dictionary<string, string>
{
    {
      "monthly_fact",
      "budget_fact"
    },
    {
      "pos_unit_sales_qty",
      "unit_sales_budget"
    },
    {
      "POS Unit Sales (Mth)",
      "POS Unit Sales (Bdgt)"
    }
};

// define measure display folder replacement
var oldStringFolder = "POS Unit Sales";
var newStringFolder = "POS Unit Sales (Budget)";

// define empty string new expression
var newExpression = "";

foreach(var m in Selected.Measures)
{

    // iterate through all pairs of replacements 
    // in order to end up with a proper substitution of
    // ▪ tables,
    // ▪ columns 
    //   and 
    // ▪ nested measure names

    foreach (var pair in replacementPairsExpression)
    {
        if (newExpression == "")
        {
            newExpression = m.Expression.Replace(pair.Key, pair.Value);
        }
        else
        {
            newExpression = newExpression.Replace(pair.Key, pair.Value);
        }
    };

    var newMeasure = m.Table.AddMeasure(
        m.Name.Replace(oldStringName, newStringName), // Name
        newExpression, // Expression
        m.DisplayFolder.Replace(oldStringFolder, newStringFolder) // Display Folder
    );

    // format DAX, inherit Format String (expressions), etc. from the original measures
    FormatDax(newMeasure);
    newMeasure.FormatString = m.FormatString;
    newMeasure.FormatStringExpression = m.FormatStringExpression;
    newMeasure.IsHidden = m.IsHidden;
}

As a side comment and pro tip, I would like to emphasize that if you want to recreate all the measure variations within a specific display folder, there is no need to manually select each measure individually. Instead, you can simply select the display folder itself as the target. By doing so, executing the C# script will automatically apply the necessary changes to all the measures residing within that folder 🙂

With just a few inputs and a few clicks, you can instantly create budget fact table-based measures from the existing actuals fact table measures in your example. By providing the necessary information and executing the appropriate steps, you can efficiently generate the desired measures without the need for manual and repetitive work.

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass