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.
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!