Backup/restore all calculation groups (workaround required for some visuals)

I needed to add a gantt chart into a report. I tried multiple visuals, but they didn’t work because they all require at least one implicit measure (for date field). An implicit measure is created by Power BI when you drag a column into a visual. And I already had calculation groups in my data model. Power BI won’t create an implicit measure if there is a calculation group in the model.

Does it mean gantt visuals can’t be used together with calculation groups? I found a workaround. Implicit measures created and added into a visuals before adding any calculation group into the model continue to work after adding a calculation group.

The workaround includes the following steps:

1) remove all calculation groups

2) enable implicit measures by changing Discourage Implicit Measures to False in Model properties (when a calculation group added this option is being changed automatically to True, when all calculation group removed it still being True, so you will need to switch it manually):

3) create a Gantt chart and add required columns to the visual fields

4) recreate calculation groups

And what if you have multiple calculation groups with multiple items? Use C# script for Tabular Editor to backup and restore all calculation groups automatically.

This script creates a backup of all calculation groups by generating another C# script that can be used to re-create all calculation groups.

using System.IO;

var file = @"c:\_TABULAR_SCRIPTS\Generated" ;

var i = true;
var k = true;
var code ="";

using(var fileWriter = new StreamWriter(file))

{

foreach ( var cg in Model.Tables.OfType<CalculationGroupTable>() )

    {

    if ( i ) { code = "var "; } else { code = ""; }

    var output = "\n\n//create calculation group " + cg.Name;
    output = output + "\n" + code + "calculationGroupTable1 = Model.AddCalculationGroup ();";
    output = output + "\n(Model.Tables[\"New Calculation Group\"] as CalculationGroupTable).CalculationGroup.Precedence = " + cg.CalculationGroup.Precedence + ";";
    output = output + "\ncalculationGroupTable1.Name = \"" + cg.Name + "\";";

    fileWriter.Write(output);

    i = false;

    foreach (var it in cg.CalculationItems ) {

        if ( k ) { code = "var "; } else { code = ""; }

        output = "\n\n//create calculation item " + it.Name;
        output = output + "\n" + code + "calculationItem1 = calculationGroupTable1.AddCalculationItem(\"" + it.Name + "\");";
        output = output + "\ncalculationItem1.Expression = @\"" + it.Expression.Replace("\"", "\"\"")  + "\";";

        if (it.FormatStringExpression != null ) {
            output = output + "\ncalculationItem1.FormatStringExpression = @\"" + it.FormatStringExpression.Replace("\"", "\"\"")  + "\";";
        }
        output = output + "\ncalculationItem1.FormatDax();";

        fileWriter.Write(output);

        k = false;

        }

    }

} 

Be aware that this basic script doesn’t backup any properties other than calculation group name, calculation item name, DAX expression and Format String Expression. Update the script or make additional changes manually if required (e.g. you probably will need to edit Ordinal property).