Fun ways for automating the selection of measures in calculation groups in Power BI.
Today’s article is connected to my LinkedIn wish list post. To summarize, I envision the ability to add a measure attribute along with a corresponding DAX function to reference this attribute. This functionality could resemble annotations or a similar concept. However, considering the current capabilities available, how can we achieve something similar to this desired feature?
// Order of magnitude calculation group
// Calculation item used for the units to thousands transformation
// 👍 Do this
IF (
SELECTEDMEASUREATTRIBUTE ( "order_of_magnitude_conversion" ) = 1,
SELECTEDMEASURE () / 1000,
SELECTEDMEASURE ()
)
// 👎 Instead of this
IF (
RIGHT ( SELECTEDMEASURENAME (), n ) = "suffix_value",
SELECTEDMEASURE () / 1000,
SELECTEDMEASURE ()
)Although it would be fantastic if that feature was already available, we’re not quite there yet. That’s why I took matters into my own hands and made an attempt to automate the selection of measures in calculation items.
As an admirer of Tabular Editor and C# scripting, I decided to explore advanced scripting to reach my goal. I approached the task using three methods: two related to measure folders (including both unique folders and the option of multiple folders / nested folders for a specific measure), and the third method focused on measure annotations.
Semantic model measures overview
Below is an overview of the semantic model measures that will be used to showcase the three scenarios mentioned above.
Scenario 1️⃣
In the first scenario, each measure will be assigned to a unique measure folder. This means that no measure will be shared across multiple folders.
In our case we will split measures into two folders: “absolutes” and “indices”
Continuing with the example of order of magnitude transformations, we will adjust the measure calculations from units to thousands or millions for the “absolutes” folder only. This adjustment is not applicable to the “indices” folder as it does not make sense to perform order of magnitude transformations on relative values.
In order to create appropriate calculation items for the “absolutes” I am going to make use of the quite recently introduced INFO.MEAURES function to get the full list of measures that are grouped into this particular folder.
Later, I can utilize the generated string as a reference and wrap it up in the EvaluateDax function within a C# script (please remember to double the quotes 😊). This enables me to execute magnitude transformations, specifically converting values to thousands or millions, exclusively for the “absolutes” measures.
// Create Calc Group based on INFO.MEASURES (single folder)
string _folder = "absolutes";
string _measures = @"{
CONCATENATEX (
FILTER(
INFO.MEASURES(),
[DisplayFolder] = """ + _folder + @"""
),
"""""""" & [Name] & """""""",
"""", """"
)
}";
// Define calc group and calc item names
string calc_group_name = "Order of magnitude";
string calc_item_name1 = "Units";
string calc_item_name2 = "Thousands";
string calc_item_name3 = "Millions";
// If calc group exists, delete it
if (Model.Tables.Contains(calc_group_name))
{
Model.Tables[calc_group_name].Delete();
}
// Add calc group
var calcGroup = Model.AddCalculationGroup();
// Add calc items
var calcItem1 = calcGroup.AddCalculationItem(calc_item_name1);
var calcItem2 = calcGroup.AddCalculationItem(calc_item_name2);
var calcItem3 = calcGroup.AddCalculationItem(calc_item_name3);
// Define calc group name
calcGroup.Name = calc_group_name;
// Define calc items expression and ordinal
// units
calcItem1.Expression = "SELECTEDMEASURENAME ()";
calcItem1.Ordinal = 10;
// thousands
calcItem2.Expression = "IF ( SELECTEDMEASURENAME () IN { " + EvaluateDax(_measures) + " }, SELECTEDMEASURE() / 1000, SELECTEDMEASURE() )";
calcItem2.Ordinal = 20;
// millions
calcItem3.Expression = "IF ( SELECTEDMEASURENAME () IN { " + EvaluateDax(_measures) + " }, SELECTEDMEASURE() / 1000000, SELECTEDMEASURE() )";
calcItem3.Ordinal = 30;An important point to note is that you can easily reuse the above script by saving it as a macro. This can be accomplished by clicking on the “+” icon, which allows you to create a new macro. You can provide a name for the macro (and even group them into some folders of your preference), add a tooltip for clarity, and define the context in which the macro should be applied. Then by a simple right click you can trigger execution of the script.
Scenario 2️⃣
While the previously mentioned code functions effectively when measures are exclusively assigned to individual folders, it may encounter issues when measures are assigned to multiple folders or nested folders. Therefore, we will now proceed to the second scenario, which requires slightly more complex DAX code to handle such situations. the DAX query is a slightly modified version of the code provided by Jason Thomas in this article.
// Create Calc Group based on INFO.MEASURES (multiple folders)
string _folder = "absolutes";
string _measures = @"
VAR _table =
ADDCOLUMNS (
SUMMARIZE ( INFO.MEASURES (), [Name], [DisplayFolder] ),
""@cnt"",
1 + LEN ( [DisplayFolder] )
- LEN ( SUBSTITUTE ( SUBSTITUTE ( [DisplayFolder], ""; "", """" ), ""\"", """" ) )
)
VAR _number_of_folders =
GENERATESERIES ( 1, MAXX ( _table, [@cnt] ) )
VAR _cross_join =
CROSSJOIN ( _table, _number_of_folders )
VAR _limited_cross_join =
FILTER ( _cross_join, [Value] <= [@cnt] )
VAR _single_folder =
ADDCOLUMNS (
_limited_cross_join,
""@folder"", PATHITEM ( SUBSTITUTE ( SUBSTITUTE ( [DisplayFolder], ""; "", ""|"" ), ""\"", ""|"" ), [Value] )
)
VAR _measure_folder_combinations =
SELECTCOLUMNS ( _single_folder, ""Measure"", [Name], ""Folder"", [@folder] )
VAR _result =
{
CONCATENATEX (
FILTER ( _measure_folder_combinations, [Folder] = """ + _folder + @""" ),
"""""""" & [Measure] & """""""",
"", ""
)
}
RETURN
_result
";
// Define calc group and calc item names
string calc_group_name = "Order of magnitude";
string calc_item_name1 = "Units";
string calc_item_name2 = "Thousands";
string calc_item_name3 = "Millions";
// If calc group exists, delete it
if (Model.Tables.Contains(calc_group_name))
{
Model.Tables[calc_group_name].Delete();
}
// add calc group
var calcGroup = Model.AddCalculationGroup();
// add calc items
var calcItem1 = calcGroup.AddCalculationItem(calc_item_name1);
var calcItem2 = calcGroup.AddCalculationItem(calc_item_name2);
var calcItem3 = calcGroup.AddCalculationItem(calc_item_name3);
// define calc group name
calcGroup.Name = calc_group_name;
// define calc items expression and ordinal
calcItem1.Expression = "SELECTEDMEASURENAME ()";
calcItem1.Ordinal = 10;
calcItem2.Expression = "IF ( SELECTEDMEASURENAME () IN { " + EvaluateDax(_measures) + " }, SELECTEDMEASURE() / 1000, SELECTEDMEASURE() )";
calcItem2.Ordinal = 20;
calcItem3.Expression = "IF ( SELECTEDMEASURENAME () IN { " + EvaluateDax(_measures) + " }, SELECTEDMEASURE() / 1000000, SELECTEDMEASURE() )";
calcItem3.Ordinal = 30;Scenario 3️⃣
Now, let’s smoothly transition to the third scenario, where we can explore the utilization of measure annotations to automate the selection of measures in calculation items. One of the key benefits of this approach is that it allows you to set up annotations without being dependent on the measure folder structure. This provides a high level of flexibility in determining which measure will be used in each calculation item.
In order to proceed, we will first need to add these annotations. This can be done either manually or through the use of a script below.
If you have multiple or nested measure folders in your semantic model, you may face limitations in selecting measures from different folders unless you hide display fodders.
// add annotations for selected measures
string annotation_name = "calc_group_annotation";
string annotation_value = "yes";
// Remove calc group annotation from all measures apart from selected ones
// It's due to the fact I did not manage to use RemoveAnnotation while iterating over Model.AllMeasures
var antiJoinMeasures = Model.AllMeasures.Except(Selected.Measures).ToList();
foreach(var m in antiJoinMeasures) {
m.RemoveAnnotation(annotation_name);
}
// Create calc group annotation for selected measures and set its value to a predefined value
foreach(var m in Selected.Measures) {
m.SetAnnotation(annotation_name, annotation_value);
}Once the annotations are added, we can use the following code to create calculation items based on the annotation values.
// Create Calc Group based on measures annotations
string annotation_name = "calc_group_annotation";
string annotation_value = "yes";
// Iterate through all model measures to capture those for which the calc group annotation has a predefined value
var measuresToBeCaptured = new List < Tuple < string >> ();
foreach(var m in Model.AllMeasures)
{
if (m.GetAnnotation(annotation_name) == annotation_value)
{
measuresToBeCaptured.Add(Tuple.Create(m.Name));
}
}
// Build up a concatenated string of all "annotated" measures
string measures_string = "";
for (int i = 0; i < measuresToBeCaptured.Count; i++)
{
measures_string += "\"" + measuresToBeCaptured[i].Item1 + "\"";
if (i < measuresToBeCaptured.Count - 1)
{
measures_string += ", ";
}
}
// Define calc group and calc item names
string calc_group_name = "Order of magnitude";
string calc_item_name1 = "Units";
string calc_item_name2 = "Thousands";
string calc_item_name3 = "Millions";
// If calc group exists, delete it
if (Model.Tables.Contains(calc_group_name)) {
Model.Tables[calc_group_name].Delete();
}
//add calc group
var calcGroup = Model.AddCalculationGroup();
//add calc items
var calcItem1 = calcGroup.AddCalculationItem(calc_item_name1);
var calcItem2 = calcGroup.AddCalculationItem(calc_item_name2);
var calcItem3 = calcGroup.AddCalculationItem(calc_item_name3);
//define calc group name
calcGroup.Name = calc_group_name;
//define calc items expression and ordinal
calcItem1.Expression = "SELECTEDMEASURENAME ()";
calcItem1.Ordinal = 10;
calcItem2.Expression = "IF ( SELECTEDMEASURENAME () IN { " + measures_string + " }, SELECTEDMEASURE() / 1000, SELECTEDMEASURE() )";
calcItem2.Ordinal = 20;
calcItem3.Expression = "IF ( SELECTEDMEASURENAME () IN { " + measures_string + " }, SELECTEDMEASURE() / 1000000, SELECTEDMEASURE() )";
calcItem3.Ordinal = 30;Don’t forget to subscribe to
and join our Power BI community
