May 2022’s release of Power BI Desktop is the best releases we’ve seen in several years. They released a new gem of a featured called FIELD PARAMETERS and this blog is going to go over the benefits to you as a developer
While there are a ton of blogs and videos already out there covering how incredible this feature is and how to set it up, I needed to figure out how to create these field parameter tables in Tabular Editor, as opposed to authoring in Power BI Desktop. (If your organization is utilizing Tabular Editor + XMLA endpoint for enterprise Power BI datasets, creating these in Power BI Desktop might not be an option.)
As many of you may need to create similar solutions, I have created this step-by-step guide to help you with creating/configuring these within Tabular Editor.
As you may know, when you create the Field parameter in the Power BI Desktop UI, this creates a DAX calculated table. You can copy that DAX expression (or free hand it if you like!) and create a Calculated Table in Tabular Editor with the same expression.
For this example, I am creating a Dimension Parameter table.
Power BI Desktop generated this DAX expression that creates 3 columns, with the fields I selected in the UI to be included in the Field parameter.
Next, I select the code and copy it to my Tabular Editor.
You’ll notice that the columns look different than in the Power BI Desktop UI.
Power BI Desktop UI:
Don’t worry, though, these can be changed to match what is seen in Power BI Desktop through a very simple process.
To change the name of the columns, you need to first change the Name Inferred in the properties from ‘True’ to ‘False’ for each of the three columns.
You can then name the 3 columns of the calculated table to match Power BI Desktop and hide the ‘Dimension Parameter Fields’ and ‘Dimension Parameter Order’ columns.
To get the same behavior as a field parameter created in Power BI Desktop, you should also set the ‘Sort By Column’ property of ‘Dimension Parameter’ and ‘Dimension Parameter Fields’ columns, to sort by ‘Dimension Parameter Order.’
Completing the Process
The last, most crucial steps, are often overlooked.
These properties transform this otherwise regular calculated table into a Field Parameter.
On the Dimension Parameter (first column) column, you need to set the Group By Columns property to Dimension Parameter Fields.
Next, you need to set a JSON extended property on the Dimension Parameter Fields column.
After that is complete, click save from Tabular Editor and then manually refresh the newly created Dimension Parameter table.
Looking to speed this process up? You’re in luck. Daniel Otykier, the creator of Tabular Editor, has provided a C# script that does all the work for us! Before you follow his link, be sure to subscribe to our blog for more tips on new features as they release!