Adding Calculation Groups using the Tabular Object Model.

Calculation groups are one the most exciting features in some time.

At the time of writing there are no tools built into Power BI to create them though. You can add them programmatically though.

To add them I wanted to play around with the tabular object model but more on this project later 🙂 . Unfortunately, there was not much documentation available on how to add calculation groups using TOM. Luckily, I have short access lines to the devs and they helped me :). I wanted to share in the code snippet below how to add a calculation group to your model in TOM using C#. Make sure you add the SSAS NuGet packages to your project.

try
{
    using (Server server = new Server())
    {
        //Connect to the server
        server.Connect(_ConnectionString);
        //Get the dekstopmodel
        Model desktopModel = server.Databases[_Model].Model;
 
        //Create a new calculation group       
        CalculationGroup calculationGroup = new CalculationGroup()
        {
            Description = "None",
            Precedence = 0,
        };

        //Create a calculation item
        CalculationItem calculationItem = new CalculationItem()
        {
            Name = "YTD",
            Expression = "CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))",
            Ordinal = 1,
            FormatStringDefinition = new FormatStringDefinition()
            {
               Expression = "0.00"
            };            
         };
        //Add the calculationitem to the calculationGroup, ofcourse 
        //usually you will add multiple
        calculationGroup.CalculationItems.Add(calculationItem);
       
        //We are creating a virtual table that needs a partition. The source is special too.
        Partition partition = new Partition()
        {
            Name = "Partition For Time Calc",
            Source = new CalculationGroupSource()
        };

        //Now create the table
        Table table = new Table()
        {
            Name = "Time Calc",
            CalculationGroup = calculationGroup
        };

        //All calculation groups need one column that you can use on your axis.
        table.Columns.Add(new DataColumn()
        {
            Name = "Calculations",
            DataType = DataType.String
        });
        
        //Now that we are done creating we need to add the calc group to the model
        var model = desktopModel;
        table.Partitions.Add(partition);
        model.Tables.Add(table);
        //Refresh the virtual table to make sure the calculations are processed
        table.RequestRefresh(RefreshType.Full);
        //Save the changes to model
        model.SaveChanges();
    }       
}

That’s it. Hope this helps.

One Reply to “Adding Calculation Groups using the Tabular Object Model.”

  1. This has been extremely useful, this is exactly what i’ve been trying to do on a project to automate several tabular projects that we have as of now, thanks!!

    I have a quick question. Is it necessary to create a partition to the calculation group?, isn’t it possible just to create the calculated group and its calculated items?. Another doubt i have, is it possible to delete the calculation group or it’s calculation items?.

    Thanks in advance!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.