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.

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.