Generating a dynamic measure selector

Key Takeaways

• A disconnected table plus a SWITCH measure makes a measure selector: Users pick measures by selecting rows on a one-column table instead of dragging fields, and a dynamic measure returns whichever they chose.
• A Tabular Editor script generates both in one go: Multi-select the measures you want, set five names at the top of the script, and run it to build the selector table and the SWITCH measure automatically.
• Multi-select across tables via the flat-list filter: When measures live on different tables, use the filter box with :ObjectType = "Measure" and flat-list results to select them all at once.

Dynamic measure selectors

For certain kinds of reports, it sometimes makes sense to be able to select which measures should be displayed by checking off members on a dimension, rather than including individual measures from the field list. There are many blogs and articles online that describe this pattern in more details. You can find them by searching for dynamic measure selector switch.

Based on a question I received on GitHub recently, this article shows you how to auto-generate a disconnected table for selection together with the SWITCH measure to go along with that table. The technique here uses a Tabular Editor script, that is executed while a number of measures are multi-selected in the explorer tree.

Script

Before executing the script below, you have to decide on a number of things:

1. What would you like the disconnected 1-column selector table to be named?
2. What would you like the column on the disconnected selector table to be named?
3. What would you like to name the dynamic switch measure?
4. On what table should the dynamic switch measure reside?
5. If no filter or more than value is filtered on the selector table, what should the dynamic measure return?

For each question, substitute the strings at the top of the script with the values you decided upon.

// (1) Name of disconnected selector table:
var selectorTableName = "Measure Selector";

// (2) Name of column on selector table:
var selectorTableColumnName = "Measure";

// (3) Name of dynamic switch measure:
var dynamicMeasureName = "Dynamic Measure";

// (4) Name of dynamic switch measure's parent table:
var dynamicMeasureTableName = "Measure Selector";

// (5) Fallback DAX expression:
var fallbackDax = "BLANK()";

// ----- Do not modify script below this line -----

if(Selected.Measures.Count == 0) {
    Error("Select one or more measures");
    return;
}

// Get or create selector table:
CalculatedTable selectorTable;
if(!Model.Tables.Contains(selectorTableName)) Model.AddCalculatedTable(selectorTableName);
selectorTable = Model.Tables[selectorTableName] as CalculatedTable;

// Get or create dynamic measure:
Measure dynamicMeasure;
if(!Model.Tables[dynamicMeasureTableName].Measures.Contains(dynamicMeasureName))
    Model.Tables[dynamicMeasureTableName].AddMeasure(dynamicMeasureName);
dynamicMeasure = Model.Tables[dynamicMeasureTableName].Measures[dynamicMeasureName];

// Generate DAX for disconnected table:
// SELECTCOLUMNS({"Measure 1", "Measure 2", ...}, "Measure", [Value])
var selectorTableDax = "SELECTCOLUMNS(\n {\n " +
    string.Join(",\n ", Selected.Measures.Select(m => "\"" + m.Name + "\"").ToArray()) +
    "\n },\n \"" + selectorTableColumnName + "\", [Value]\n)";

// Generate DAX for dynamic metric:
// VAR _s = SELECTEDVALUE('Metric Selection'[Value]) RETURN SWITCH(_s, ...)
var dynamicMeasureDax = 
    "VAR _s =\n SELECTEDVALUE('" + selectorTableName + "'[" + selectorTableColumnName + "])\n" +
    "RETURN\n SWITCH(\n _s,\n " +
    string.Join(",\n ", Selected.Measures.Select(m => "\"" + m.Name + "\", " + m.DaxObjectFullName).ToArray()) +
    ",\n " + fallbackDax + "\n )";

// Assign DAX expressions:
selectorTable.Expression = selectorTableDax;
dynamicMeasure.Expression = dynamicMeasureDax;

Using the script

Simply paste the script into Tabular Editor, select a number of measures in the explorer tree (hold SHIFT or CTRL to multi-select). If your measures are scattered across multiple tables, Tabular Editor will not let you multiselect them (as you cannot select objects from different parts of the tree at once). However, a workaround is to use the filter functionality with search results shown in a flat list (click the button at the very right of the screen). For example, you can put a wildcard * in the filter box, or type :ObjectType = "Measure" to restrict the search to only show measures.

Once you selected the measures you want to include in the measure selector and the dynamic measure, hit F5 to execute the script. If there was a mistake, you can always hit CTRL+Z to undo the effects of running the script.

For example, if I run the script with this selection of measures:

The script sets the calculated table expression as follows:

…and the dynamic switch measure expression as follows:

For further reading

• Power BI Tricks – WhatIf parameters (Tabular Editor). Same scripting pattern, but for WhatIf parameter tables instead of a measure selector.
• Tabular Editor Tricks – Object State (Tabular Editor). Another TE scripting trick in the same mold; worth a read to see how far you can take C# scripts for model automation.
• C# scripting PBIR (Tabular Editor). If you want to push scripting into the report layer too, not just the model, this is the place to start.
• SWITCH (DAX Guide). The full SWITCH reference, if you want to understand what's going on inside the generated expression.
• C# Scripts (Tabular Editor). The scripting API docs; useful when you want to tweak anything in the script or understand what Selected and Model expose.

In conclusion

Building a dynamic measure selector by hand means writing out a disconnected table and a long SWITCH measure, and keeping them in sync every time the list of measures changes. The script here does both for you: select the measures, set a few names, hit F5, and you’ve got a working selector table and dynamic measure. And if the result isn’t quite what you wanted, CTRL+Z undoes the whole thing so you can tweak your selection and run it again.