Tabular Editor: Explicit Calculation Group Measures

by

I am always looking for optimizations to facilitate real-world use cases. In this article, we had a requirement to create every possible variation of our time intelligence calculation group for every base measure in a Power BI semantic model.

Why explicit measures? Calculation groups are awesome. However, they lack some customizability in ordering, etc. and the biggest drawback? Power BI tables and matrices are unable to sort by specific measures when sliced by the calculation group’s calculation Item. For example, if my calculation group includes calculation items:

  • Current
    • currently filtered dates
  • Prior Year
    • dates corresponding to the prior year values of the currently filtered dates

If I create a matrix with the calculation item and measures as the column values and customer on rows, I am unable to sort by a specific measure + calculation item combination. For example, I am unable to sort by [Sales] + calculation item [Prior Year] to sort my customers by most sales in the prior year.

Why automation? If you have a small model with a small number of base measures…okay, maybe this is overkill. But, the a lot of us work in large enterprise models with hundreds to thousands of base measures.

  1. Manually creating explicit measures for each would be the biggest waste of time and resources ever. “But I can just copy and paste the TMDL code and repoint to a new measure and/or calculation item”. Sure…still slow.
  2. When adding new base measures, you have to also create n number of explicit measures based on your requirements. This script can be run anytime to pick up new base measures and create their explicit time intelligence measures.

There are several scripts that provided inspiration.

Without further ado, here is my current iteration with some proposals for enhancement.

// source: https://github.com/PowerDAX/tabular-editor
// altered to allow overwrite
// altered for our environment
// altered to set format strings properly
// altered to use all base measures from all tables

// variables
// 1 overwrites existing measures, 0 preserves existing measures
// proposed enhancement: utilize an annotation to determine if a measure should be overwritten. this would allow you to overwrite all measures except for those annotated to not overwrite.
// proposed enhancement: do not delete measure, just set values
var overwriteExistingMeasures = 0; 
// enter your calculation group
// proposed enhancement: change to a list to allow for multiple calculation groups
var timeIntelligenceCalculationGroup = "Time Intelligence";
var timeIntelligenceCalculationItem = "Time Calculation";
// used to ensure we do not create explicit time intelligence measures for measures outside of the base measures
// proposed enhancement: change to a list to allow for multiple base measure display folders
var baseMeasuresDisplayFolder = "Measures";

// create measures from calculation items
// filters out some tables and measures that match a string
// proposed enhancement: change to a list to allow for multiple tables
// proposed enhancement: change to a list to allow for multiple strings
var baseMeasures = Model.AllMeasures.Where(
    m => (m.DisplayFolder.StartsWith(baseMeasuresDisplayFolder)
    && !m.Table.Name.StartsWith("Parameter")
    && !m.Table.Name.StartsWith("Dynamic")
    && !m.Table.Name.StartsWith("Dim Calendar")
    && !m.Name.Contains("Latest")
    && !m.Name.Contains("Growth")
    && !m.IsHidden)).ToList();

foreach(var measure in baseMeasures)
{
    var baseMeasureTable = measure.Table;
    {
        foreach(var calcItem in (Model.Tables[timeIntelligenceCalculationGroup] as CalculationGroupTable).CalculationItems)
        {
            
            // measure name
            string measureName = measure.Name + " " + calcItem.Name;

            if( overwriteExistingMeasures == 1 )
            {
                foreach( var m in Model.AllMeasures.Where( m => m.Name == measureName ).ToList() ) 
                {
                    m.Delete();
                }
            }

            // only if the measure is not yet there (think of reruns)
            if(!Model.AllMeasures.Any(x => x.Name == measureName && !x.IsHidden))
            {
                // add measure
                var newMeasure = baseMeasureTable.AddMeasure(
                    // measure name = base measure name + calculation item name
                    measure.Name + " " + calcItem.Name,
                    // dax expression = CALCULATE( base measure, calculation group = calculation item )
                    "CALCULATE( " + measure.DaxObjectFullName + ", '" + timeIntelligenceCalculationGroup + "'[" + timeIntelligenceCalculationItem + "]= \"" + calcItem.Name + "\" )"
                );

                // set display folder = calculation group name + measure name
                newMeasure.DisplayFolder = measure.DisplayFolder.Replace(baseMeasuresDisplayFolder,timeIntelligenceCalculationGroup) + "\\" + measure.Name;
    
                // set format string
                if(calcItem.Name.Contains("%"))
                // = % if name includes % (i.e. YOY %, etc.)
                {
                    newMeasure.FormatString = "#,##0.00 %;(#,##0.00 %)";
                } else {
                    newMeasure.FormatString = measure.FormatString;
                }
                // otherwise, to the format string of the base measure
            }
        }
    }
}

Let’s break this down.

// variables
// 1 overwrites existing measures, 0 preserves existing measures
// proposed enhancement: utilize an annotation to determine if a measure should be overwritten. this would allow you to overwrite all measures except for those annotated to not overwrite.
// proposed enhancement: do not delete measure, just set values
var overwriteExistingMeasures = 0; 
// enter your calculation group
// proposed enhancement: change to a list to allow for multiple calculation groups
// proposed enhancement: do not delete measure, just set values
var timeIntelligenceCalculationGroup = "Time Intelligence";
var timeIntelligenceCalculationItem = "Time Calculation";
// used to ensure we do not create explicit time intelligence measures for measures outside of the base measures
// proposed enhancement: change to a list to allow for multiple base measure display folders
var baseMeasuresDisplayFolder = "Measures";

overwriteExistingMeasures

  • 1 overwrites existing measures, 0 preserves existing measures
  • this allows for overwriting measures or only creating net new / missing measures
  • why not overwrite all? if you add custom, domain-specific logic to a measure, this would allow you to “lock in” that measure definition and not overwrite w/ standard logic
  • proposed enhancement: utilize an annotation to determine if a measure should be overwritten. this would allow you to overwrite all measures except for those annotated to not overwrite.
  • proposed enhancement: do not delete measure, just set values
    • deleting a measure may set the measure lineageTag to a new value
    • why does this matter? it will clutter your git diff even though the measure logic didn’t change

timeIntelligenceCalculationGroup

  • this script creating explicit measures for a specific calculation group
  • setting this variable defines which calculation group to create explicit measures for

timeIntelligenceCalculationItem

  • we could retrieve this value but this covers you in the case of multiple calculation items

baseMeasuresDisplayFolder

  • we utilize this to restrict the base measures to create time intelligence measures for
  • for example, we have row count measures in a hidden “_Row Count” display folder that do not require time intelligence measures…or measures built for specific purposes
// create measures from calculation items
// filters out some tables and measures that match a string
// proposed enhancement: change to a list to allow for multiple tables
// proposed enhancement: change to a list to allow for multiple strings
var baseMeasures = Model.AllMeasures.Where(
    m => (m.DisplayFolder.StartsWith(baseMeasuresDisplayFolder)
    && !m.Table.Name.StartsWith("Parameter")
    && !m.Table.Name.StartsWith("Dynamic")
    && !m.Table.Name.StartsWith("Dim Calendar")
    && !m.Name.Contains("Latest")
    && !m.Name.Contains("Growth")
    && !m.IsHidden)).ToList();

baseMeasures

  • this is where we set a list of base measures to build explicit time intelligence measures for
  • this list is filtered to
    • those in the baseMeasuresDisplayFolder
    • those not in a Parameter table
    • those not in a Dynamic table (i.e. for my dynamic measures)
    • those not in my Dim Calendar table
    • those that do not contain the word “Latest” (whose purpose is to show the true latest regardless of date/time)
    • those that do not contain the word “Growth” (another specific purpose measure)
    • those that are not hidden (our hidden measures are usually “helper” measures
foreach(var measure in baseMeasures)

foreach loop for each measure in our baseMeasures list

    var baseMeasureTable = measure.Table;

setting a baseMeasureTable variable to the table in which the base measure resides to be utilized when new explicit time intelligence measures are created (what table to create them in)

        foreach(var calcItem in (Model.Tables[timeIntelligenceCalculationGroup] as CalculationGroupTable).CalculationItems)

foreach loop for each calculation group calculation item (i.e. prior year, year over year, month to date, year to date, etc.)

            // measure name
            string measureName = measure.Name + " " + calcItem.Name;

set the new explicit time intelligence measure name to the base measure name + the calculation item name (i.e. Sales Dollars + YTD = Sales Dollars YTD)

            if( overwriteExistingMeasures == 1 )
            {
                foreach( var m in Model.AllMeasures.Where( m => m.Name == measureName ).ToList() ) 
                {
                    m.Delete();
                }
            }

if overwriteExistingMeasures = 1, delete the matching measureName we just calculated

  • proposed enhancement: do not delete measure, just set values
    • deleting a measure may set the measure lineageTag to a new value
    • why does this matter? it will clutter your git diff even though the measure logic didn’t change
            // only if the measure is not yet there (think of reruns)
            if(!Model.AllMeasures.Any(x => x.Name == measureName && !x.IsHidden))
            {
                // add measure
                var newMeasure = baseMeasureTable.AddMeasure(
                    // measure name = base measure name + calculation item name
                    measure.Name + " " + calcItem.Name,
                    // dax expression = CALCULATE( base measure, calculation group = calculation item )
                    "CALCULATE( " + measure.DaxObjectFullName + ", '" + timeIntelligenceCalculationGroup + "'[" + timeIntelligenceCalculationItem + "]= \"" + calcItem.Name + "\" )"
                );

if measure does not exist, create new measure in the baseMeasureTable equal to base measure.Name + " " + calcItem.Name with a DAX expression = CALCULATE( " + measure.DaxObjectFullName + ", '" + timeIntelligenceCalculationGroup + "'[" + timeIntelligenceCalculationItem + "]= \"" + calcItem.Name + "\" )

  • i.e. CALCULATE( [Sales Dollars], 'Time Intelligence'[Time Calculation] = "YTD" )
                // set display folder = calculation group name + measure name
                newMeasure.DisplayFolder = measure.DisplayFolder.Replace(baseMeasuresDisplayFolder,timeIntelligenceCalculationGroup) + "\\" + measure.Name;

set the new explicit time intelligence measure’s display folder to the time intelligence calculation group name + base measure name (i.e. Time Intelligence\Sales Dollars)

                // set format string
                if(calcItem.Name.Contains("%"))
                // = % if name includes % (i.e. YOY %, etc.)
                {
                    newMeasure.FormatString = "#,##0.00 %;(#,##0.00 %)";
                } else {
                    newMeasure.FormatString = measure.FormatString;
                }
                // otherwise, to the format string of the base measure

set the new explicit time intelligence measure’s format string expression to the base measure’s format string expression unless the calculation group’s calculation item contains % specifying it as a percentage measure.

I also modified this script slightly to allow a selection of base measures to create the explicit time intelligence measures for.

That’s it. Other scripts required selecting specific measures and/or multiple steps including deleting a temporary calculation group. This script allows for running against non-time intelligence calculation groups, creating lists of calculation groups, further limiting the measures to overwrite or not, etc.

I hope it helps you in your environment to optimize and speed up your development.

https://github.com/PowerDAX/tabular-editor/blob/main/calculation-groups/

(Visited 1 times)