A very useful Add On to Power BI is called “Tabular Editor”. Not only does it allow you to bulk maintain all measures and the data model it also lets you use scripts written in C# to automate manual tasks even further.
In this post, I want to share two use cases where scripting has proven invaluable for me
- Generating all Calculation Items as Measures
- Generating measures for Hundreds of KPI columns
Generating all Calculation Group Items as Measures
Calculations Groups are great! You can use them to quickly create variations like “Last Year Value”, “Current Year – Last Year”, and “% diff” of Measures that use the same logic. This saves a lot of time in creation and maintenance because you only need to write the DAX once. Here’s an example from a customer project:
The customer has several base measures like “Demand”, “Net Sales”, “Gross Sales”, “Net Sales Volume” etc.
All of them have a long list of variations that are used with them:
With “SELECTEDMEASURE” you can calculate, e.g., the last fiscal year (starts in March) values for all base measures above. Without having to write 5 separate measures:
IF (
HASONEVALUE ( 'DIM_DATE_FINANCE'[FISCAL_YEAR] )
&& HASONEVALUE ( 'DIM_DATE_FINANCE'[CALENDAR_WEEK_FISCAL_YEAR] ),
CALCULATE (
SELECTEDMEASURE (),
FILTER (
ALL ( 'DIM_DATE_FINANCE' ),
'DIM_DATE_FINANCE'[FISCAL_YEAR] = VALUES ( 'DIM_DATE_FINANCE'[FISCAL_YEAR] ) - 1
&& 'DIM_DATE_FINANCE'[CALENDAR_WEEK_FISCAL_YEAR] = VALUES ( 'DIM_DATE_FINANCE'[CALENDAR_WEEK_FISCAL_YEAR] )
&& 'DIM_DATE_FINANCE'[DATE_ID] <= MAX ( 'DIM_DATE_FINANCE'[DATE_ID] )
)
)
)
However, Calculation Groups have some limitations, and especially colleagues that use the dataset for their own reports and are not experts in Power BI might struggle at first with the concept. That’s why we looked for a way to combine the advantages of calculation groups with the flexibility and easy-to-use nature of Measures:
Below C# script takes all Base Measures (marked with IST) and creates separate Measures for all calculation items:
string[] calculation_items ={ "VJ", "AJ", "total zu VJ","% zu VJ","Jahr AJ",
"Jahr VJ", "Jahr total zu VJ","Jahr % zu VJ",
"akt. Woche AJ","akt. Woche VJ","VJ hidden",
"hidden % zu VJ","Jahr %Pkt. zu VJ","kum VJ", "kum AJ"};
var source_table = Model.Tables["FACT_COIN_OSS_AGGDAILY"];
var target_table = Model.Tables["FACT_COIN_OSS_AGGDAILY"];
var all_possible_source_measures = source_table.Measures.ToArray();
//either use # as appendix in folder names or replace this # by specific folder name
var filtered_source_measures = all_possible_source_measures.Where(
measure
=> measure.DisplayFolder.Contains("#"));
var generated_measures = new System.Collections.Generic.List<Tuple<string,string,string>>();
// for each item in list
foreach (var current_measure in filtered_source_measures)
{
if(current_measure.Name.Contains("IST"))
{
foreach (var item in calculation_items)
{
// Generate the expression of the new measure
var generatedExpression = string.Format(
@"CALCULATE({0}, Zeitvergleiche[Ausprägung]=""{1}"")",current_measure.DaxObjectName,item);
var newMeasureName = current_measure.Name.Replace("IST", item).Replace(" ", " ");
var target_directory = current_measure.DisplayFolder;
generated_measures.Add(new Tuple<string, string, string>(newMeasureName, generatedExpression, target_directory));
}
}
}
var debugMessage = new System.Text.StringBuilder();
if (generated_measures.Count()> 0)
{
debugMessage.AppendLine("Generated Measure '" + generated_measures[0].Item1 + "'");
debugMessage.AppendLine("----------------------------------------------");
debugMessage.AppendLine(generated_measures[0].Item2);
debugMessage.AppendLine("");
debugMessage.AppendLine("...");
debugMessage.AppendLine("");
debugMessage.AppendLine(generated_measures.Count() + " items");
}
var dialogResult = System.Windows.Forms.MessageBox.Show(debugMessage.ToString(),
"execute?", System.Windows.Forms.MessageBoxButtons.YesNo);
if (dialogResult == System.Windows.Forms.DialogResult.No)
{
return;
}
//delete measures with autogen as Data Category flag
foreach(var m in filtered_source_measures.Where(m => m.DataCategory == "autogen").ToList())
{
m.Delete();
}
foreach (var generated_measure in generated_measures)
{
var created_measure = target_table.AddMeasure(generated_measure.Item1);
created_measure.Expression = generated_measure.Item2;
created_measure.DisplayFolder = generated_measure.Item3;
created_measure.DataCategory = "autogen";
//edit format of measures
if (created_measure.Name.Contains("%"))
created_measure.FormatString = "0.00 %;-0.00 %;0.00 %";
else if (created_measure.Name.Contains("Stk"))
created_measure.FormatString = "#,0";
else created_measure.FormatString = "#,0.00 €;-#,0.00 €;#,0.00 €" ;
}Doing this task manually would take forever.
Generating measures for Hundreds of KPI columns
With another project, the goal was to create an overview of ~200 different Measures. Each of them with a lot of variations. Just creating the Base Measures that are needed for calculation groups would have taken forever. But there’s an easy way to create all base Measures with C#:
For each selected column, a new base(IST) Measure will be created:
// Creates a IST measure for every currently selected column
foreach(var c in Selected.Columns)
{
var newMeasure = c.Table.AddMeasure(
c.Name.Replace("_", "") + " IST", // Name
"CALCULATE ( SUM ( " + c.DaxObjectFullName + " ), DATE_REF[IS_CURR_FISCAL_YEAR] = \"Y\")", // DAX expression
"Measures Zeitverläufe" // Display Folder
);
// Set the format string on the new measure:
newMeasure.FormatString = ",##0.0 €";
}
Instead of using calculation groups to store the logic (Chapter 1), it could be an option to have the logic in C# and don’t use calculation groups at all. In that case, for each variation of the base Measure, the code must be maintained in the script. e.g.:
// Creates a % zu VJ measure for every currently selected column
foreach(var c in Selected.Columns)
{
var newMeasure = c.Table.AddMeasure(
c.Name.Replace("_", "") + " % zu VJ", // Name
"VAR VJ = CALCULATE ( SUM ( " + c.DaxObjectFullName + " ), DATE_REF[IS_PREV_FISCAL_YEAR] = \"Y\") \n" +
"VAR IST = CALCULATE ( SUM ( " + c.DaxObjectFullName + " ), DATE_REF[IS_CURR_FISCAL_YEAR] = \"Y\") \n" +
"RETURN DIVIDE (IST - VJ, VJ, BLANK())", // DAX expression
"Measures Zeitverläufe" // Display Folder
);
// Set the format string on the new measure:
newMeasure.FormatString = ",##0.0 %";
}
Summary
There are a lot of smart use cases for C# to automate your PowerBI datasets. The bottom line of all of them is that you save a lot of time! With C#, you can even control tables and their relationships. But that is for another post.
I hope this post inspires you to start automating your datasets. You don’t have to be a pro in C# to do it.