Update May 29th 2019: It turns out that the technique outlined here can be used for SSAS or Azure Analysis Services Tabular models of Compatibility Level 1400+. In fact, just by adding the ParameterMetadata Extended Property (step 9 below) to any numeric column in your model, you can instruct Power BI to display a single-select slider as the default visual for the column.
I received a comment on my introductory webinar video, regarding whether Tabular Editor could be used to generate lots of WhatIf parameters quickly.
Well, behind the scenes, a WhatIf parameter is simply a Calculated Table with a single column, defined using the GENERATESERIES DAX function (to hold all the possible values of the parameter), along with a measure that uses the SELECTEDVALUE function to return the currently filtered value on the table (or a default value, if nothing or multiple values are filtered).
All of this can easily be added using Tabular Editor, although doing it is not officially supported by Microsoft, so as usual, when modifying your Power BI models through Tabular Editor, make sure to save a backup of your .pbix or .pbit file. By default, Tabular Editor will restrict the kind of changes we can apply to a Power BI model, to prevent things from breaking. However, in order to create WhatIf parameters through Tabular Editor, we need to lift this restriction, as we need to add new Calculated Tables to the model. So make sure to check the “Allow unsupported Power BI features” checkbox under File > Preferences > General:
As of the March 2019 version of Power BI, connecting Tabular Editor directly to a Power BI model that is loaded within an instance of Power BI Desktop…
…does not let us add measures (and possibly other things). We would get an error message similar to the following, when trying to save the model changes:
Failed to save modifications to the server. Error returned: 'Unexpected column name: Received column 'ObjectID.Expression' in rowset 'ObjectTranslations'. Expected column 'ObjectID.Set'.'.
In general when encountering this error, a possible workaround is to export the Power BI model as a template (.pbit file), open the .pbit file within Tabular Editor, reapply the changes and then save the file. When reopening the .pbit file in Power BI Desktop, you may encounter some issues depending on what was changed. In my experience, these can often be overcome by refreshing the data or by simply adding and removing a measure within Power BI Desktop. But not always – hence the unsupported warning.
However, for adding WhatIf parameters to a .pbit file with Tabular Editor, if you follow the steps below carefully, you should be good (at least with the March 2019 version of Power BI – who knows what happens in future versions. By the way, if you would like Microsoft to officially support making changes through tools such as Tabular Editor, make sure to give this idea a vote):
Easy peasy, isn’t it? No? Well, luckily, there’s a much better way to perform the time-consuming steps 3-10.
Paste the following code into Tabular Editors Advanced Scripting tab:
// Parameter settings:
var paramName = "MyParam";
var paramMin = 0;
var paramMax = 100;
var paramIncrement = 10;
var paramDefault = 50;
var paramDataType = DataType.Int64;
// Invariant Culture. When used in string.Format, we ensure that decimal numbers are formatted
// with a . (period) as decimal separator, which is the standard way Tabular Editor writes DAX:
var c = System.Globalization.CultureInfo.InvariantCulture;
// Add a new calculated table to the model:
var table = Model.AddCalculatedTable(paramName,
string.Format(c, "GENERATESERIES({0},{1},{2})", paramMin, paramMax, paramIncrement));
// Add the Calculated Table column:
var column = table.AddCalculatedTableColumn(paramName, "[Value]", "", paramDataType);
column.SummarizeBy = AggregateFunction.None;
// Set Extended Property on the Calculated Table Column:
column.SetExtendedProperty("ParameterMetadata", "{\"version\":0}", ExtendedPropertyType.Json);
// Add the WhatIf parameter measure:
table.AddMeasure(paramName + " Value",
string.Format(c, "SELECTEDVALUE({0}, {1})", column.DaxObjectFullName, paramDefault));
To use the script, modify the settings in the top section to suit your needs, and hit F5. That’s it. Steps 3-10 completed in a fraction of a second!
If you want to get really advanced, you can create a text file containing the settings of multiple WhatIf parameters:
MyParam1,0,100,10,50,Int64
MyParam2,100,200,10,150,Int64
MyParam3,-1,1,0.1,0,Double
Save this file somewhere on your machine, then modify the script to set the settings based on the contents of this file, and create all 3 parameters in one go:
// Modify below to point to the file that holds your WhatIf parameter settings:
var settings = System.IO.File.ReadLines(@"c:\WhatIf\WhatIfSettings.csv");
var c = System.Globalization.CultureInfo.InvariantCulture;
foreach(var setting in settings)
{
var settingArray = setting.Split(',');
// Parameter settings:
var paramName = settingArray[0];
var paramMin = decimal.Parse(settingArray[1],c);
var paramMax = decimal.Parse(settingArray[2],c);
var paramIncrement = decimal.Parse(settingArray[3],c);
var paramDefault = decimal.Parse(settingArray[4],c);
var paramDataType = (DataType)Enum.Parse(typeof(DataType), settingArray[5]);
// Add a new calculated table to the model:
var table = Model.AddCalculatedTable(paramName,
string.Format(c, "GENERATESERIES({0},{1},{2})", paramMin, paramMax, paramIncrement));
// Add the Calculated Table column and set SummarizeBy to None:
var column = table.AddCalculatedTableColumn(paramName, "[Value]", "", paramDataType);
column.SummarizeBy = AggregateFunction.None;
// Set Extended Property on the Calculated Table Column:
column.SetExtendedProperty("ParameterMetadata", "{\"version\":0}", ExtendedPropertyType.Json);
// Add the WhatIf parameter measure:
table.AddMeasure(paramName + " Value",
string.Format(c, "SELECTEDVALUE({0}, {1})", column.DaxObjectFullName, paramDefault));
}
After executing the script, save the .pbit file and close Tabular Editor. Then open the file in Power BI Desktop and behold your brand new auto-generated WhatIf parameters!
Feel free to leave comments/feedback/questions below!