If you have ever created a Power BI report with live connection to an Analysis Services Tabular model (SQL Server Analysis Services or Azure Analysis Services), you might have wondered if it is possible to use field parameters in such a report.

The answer is yes!

The main point to remember is that field parameters must be defined in the model the report is live-connected to, whether it is an Analysis Services or Power BI semantic model. This is because field parameters are tables set up with specific properties. There is no such thing as “report-level Field Parameters”. (But see below for a discussion of Composite Models).

So how do we create field parameters in Analysis Services models?

When creating field parameters in Analysis Services models, the key differences from Power BI models are:

1. The model’s Compatibility Level must be 1400+, otherwise Extended Properties properties are not available, which are needed for the “fields” column. For Compatibility Level 1400+ to be available, we need SQL Server 2017 Analysis Services or later.
2. Field parameters must be created using a tool such as Tabular Editor or Visual Studio, since the Power BI Desktop interface is not available.
3. The Group By Columns property is not available for any Analysis Services models (as of today), so we must instead set the Key = True for the “fields” column and Keep Unique Rows = True for the “name” column (discussion of this method here on SQLBI.com).
4. For SQL Server 2019 Analysis Services or earlier (build 15.x.x.x or earlier) , the DAX NAMEOF function is not available, so the values in the “fields” column must be hard-coded rather than defined using
   NAMEOF ( <column or measure reference> ).

An existing Tabular Editor C# script is available here on TabularEditor.com (credit to Daniel Otykier), that creates a field parameter table in a Power BI model based on columns or measures selected. The steps are also described in this P3 Adaptive post.

I have updated the above script taking into account all of the above requirements, so that it creates an appropriate field parameter table correctly for either Power BI or Analysis Services models, using either Tabular Editor 2 or Tabular Editor 3. Here it is:

// This script is based on the script by Daniel Otykier (https://twitter.com/dotykier) available here:
// https://docs.tabulareditor.com/common/CSharpScripts/Beginner/script-create-field-parameter.html

// Updated by Owen Auger (https://twitter.com/Ozeroth) 2024-09-20

// Before running the script, select the measures and/or columns that you
// would like to use as field parameters (hold down CTRL to select multiple
// objects). Also, you may change the name of the field parameter table
// below. NOTE: If used against Power BI Desktop, you must enable unsupported
// features under File > Preferences (TE2) or Tools > Preferences (TE3).
using System.Text.RegularExpressions;

// Name for Field Parameter table
var name = "Parameter";

// Check Compatibility Level >= 1400
var compatibilityLevel = Model.Database.CompatibilityLevel;
if (compatibilityLevel < 1400 ){
    Info("Compatibility Level of 1400+ required for Field Parameters. Current Compatibility Level is " + compatibilityLevel + ".");
    return;
}

// Determine if TE2
var te2 = Selected.GetType().Assembly.GetName().Version.Major == 2;

// Determine if Compatibility Mode is Power BI
var isPowerBI = Model.Database.CompatibilityMode.Equals(CompatibilityMode.PowerBI);

// Extract Analysis Services major version
//   SSAS 2017 => 14,
//   SSAS 2019 => 15
//   SSAS 2022 => 16
//   AAS       => 16 (Sep 2024)
//   Power BI  => 16 (Sep 2024)
//   Unknown   => 0
int asVersion = 0;

// Regex to match consecutive digits before the dot
string asVersionFull = Model.Database.ServerVersion;
string pattern = @"\D*(\d+)\.";
Match asVersionMatch = Regex.Match(asVersionFull, pattern);
if (asVersionMatch.Success)
{
    Int32.TryParse(asVersionMatch.Groups[1].Value, out asVersion);
}

// NAMEOF DAX function is available for version 16+
var useNAMEOF = asVersion >= 16;

// Throw exception if no measures or columns selected
//if(Selected.Columns.Count == 0 && Selected.Measures.Count == 0) throw new Exception("No columns or measures selected!");
if(Selected.Columns.Count == 0 && Selected.Measures.Count == 0) {
    Info("No columns or measures selected!");
    return;
}

// Construct the DAX for the calculated table based on the current selection
// by concatenating Columns & Measures
var objects = Selected.Columns.Cast<ITabularTableObject>().Concat(Selected.Measures);
string daxFormat;
if (useNAMEOF) {
    daxFormat = "( \"{0}\", NAMEOF ( '{1}'[{0}] ), {2} )";
}
else {
    daxFormat = "( \"{0}\", \"'{1}'[{0}]\", {2} )";
}
string dax = "{\n    " + string.Join(",\n    ", objects.Select((c,i) => string.Format(daxFormat, c.Name, c.Table.Name, i))) + "\n}";

// Add the calculated table to the model:
var table = Model.AddCalculatedTable(name, dax);

// In TE2 columns are not created automatically from a DAX expression, so 
// we will have to add them manually:
var nameColumn = te2 ? table.AddCalculatedTableColumn(name, "[Value1]") : table.Columns["Value1"] as CalculatedTableColumn;
var fieldColumn = te2 ? table.AddCalculatedTableColumn(name + " Fields", "[Value2]") : table.Columns["Value2"] as CalculatedTableColumn;
var orderColumn = te2 ? table.AddCalculatedTableColumn(name + " Order", "[Value3]") : table.Columns["Value3"] as CalculatedTableColumn;

// Set Data Type Inferred = true for all columns (probably only needed for TE2)
nameColumn.IsDataTypeInferred = true;
fieldColumn.IsDataTypeInferred = true;
orderColumn.IsDataTypeInferred = true;


// Rename the columns that were added automatically in TE3:
if(!te2) {
    nameColumn.IsNameInferred = false;
    nameColumn.Name = name;
    fieldColumn.IsNameInferred = false;
    fieldColumn.Name = name + " Fields";
    orderColumn.IsNameInferred = false;
    orderColumn.Name = name + " Order";
}
// Set remaining properties for field parameters to work
// See: https://twitter.com/markbdi/status/1526558841172893696

if(isPowerBI)
// For Power BI, set Group By Columns property of nameColumn
{
    nameColumn.GroupByColumns.Add(fieldColumn);
}
else
// For Analysis Services models, Group By Columns property is not available
// so instead set the following:
//    fieldColumn: Key = true
//    nameColumn:  Keep Unique Rows = true

{
    fieldColumn.IsKey = true;
    nameColumn.KeepUniqueRows = true;
}

// Set Sort By, Visibility, and Extended Properties
nameColumn.SortByColumn = orderColumn;
fieldColumn.SortByColumn = orderColumn;
fieldColumn.SetExtendedProperty("ParameterMetadata", "{\"version\":3,\"kind\":2}", ExtendedPropertyType.Json);
fieldColumn.IsHidden = true;
orderColumn.IsHidden = true;

The main updates I have made to handle the different methods for creating field parameter tables are:

• Display a message and exit if Compatbility Level is not 1400+.
• If Compatibility Mode is “Power BI”, then use Group By Columns, otherwise Key and Keep Unique Rows.
• If Analysis Services build is less than 16.x.x.x then hard-code the references in the “fields” column rather than using NAMEOF.
• Allow a mixture of columns and measures to be included in the field parameter.
• Set Infer Data Type = True for all columns of the table. This is a precaution, as I had noticed the “order” column type being set to String in Tabular Editor 2.

That’s it for the main part of this post! Feel free to use (and improve on) the above script! 😃

Please read on for some more background detail on field parameters and detail on the steps carried out by the script in case you wanted to carry them out manually.

What is a field parameter?

For this post I’m assuming we’re talking about “standard” field parameters with the same appearance and functionality as those created user the New Parameter interface in Power BI Desktop, created with calculated tables.

When you create a field parameter using the Power BI Desktop interface, Power BI creates a three-column calculated table of this form:

Parameter =
{
    ( "City", NAMEOF ( 'Customer'[City] ), 0 ),
    ( "Country-Region", NAMEOF ( 'Customer'[Country-Region] ), 1 ),
    ( "Postal Code", NAMEOF ( 'Customer'[Postal Code] ), 2 )
}

The columns are automatically renamed according to the parameter name provided in the dialog box. For example, if the name “Parameter” is provided, the columns would be named Parameter, Parameter Fields and Parameter Order.

These column properties are also set:

• Extended Properties: The JSON property ParameterMetadata: {"version":3, "kind":2} instructs Power BI to treat the measures/columns referenced in the “fields” column to act as the parameter values. You could create a field parameter table with just the “fields” column; it would be ugly but it would work!
• Group By Columns: Ensures that when the “name” column is placed in a visual’s field well, the “fields” column is also included in the DAX query, effectively allowing the “name” column to function as an alias (display name) of the “fields” column.

Manually creating an Analysis Services field parameter table with Tabular Editor

Suppose that we want to create a field parameter allowing selection of three columns:

1. User[ID]
2. User[Name]
3. User[Age]

Step 1: Create the field parameter table as a calculated table:

(a) For SQL Server 2017 or 2019 Analysis Services (since the NAMEOF function is not available):

User Parameter =
{
    ( "ID", "'User'[ID]", 0 ),
    ( "Name", "'User'[Name]", 1 ),
    ( "Age", "'User'[Age]", 2 )
}

(b) For SQL Server 2022+ Analysis Services or Azure Analysis Services:

User Parameter =
{
    ( "ID", NAMEOF ( User[ID] ), 0 ),
    ( "Name", NAMEOF ( User[Name] ), 1 ),
    ( "Age", NAMEOF ( User[Age] ), 2 )
}

Step 2: Rename the columns

For each column, set Name Inferred = False and Data Type Inferred = True.

Then rename the columns to:

Step 3: Set column properties

In Power BI models, the “name” column’s Group By Columns property is set to the “fields” column. However, Analysis Services models do not support the Group By Columns property.

Instead, we have to use the Key and Keep Unique Rows properties similar to the method described here:

1. For the “field” column set
   • Key = True
   • Sort By = “order” column
2. For the “name” column set
   • Keep Unique Rows = True
   • Sort by = “order” column
3. For the “order” column set
   • Hidden = True

Handling duplicate fields

Field parameter tables created in Analysis Services models following the above process cannot contain duplicate values in the “fields” column, since it is set as the Key column. This is not an issue for standard field parameter tables since each column/measure appears only once, but more exotic field parameter tables might contain the same field repeated across multiple “groups”.

Here is a valid field parameter table for a Power BI semantic model containing repeated values in the “fields” column and a fourth column “Report Group”:

Customer Parameter with Groups Power BI =
{
    ( "City", NAMEOF ( 'Customer'[City] ), 0, "Sales Report" ),
    ( "Country-Region", NAMEOF ( 'Customer'[Country-Region] ), 1, "Sales Report" ),
    ( "Postal Code", NAMEOF ( 'Customer'[Postal Code] ), 2, "Sales Report" ),
    ( "City", NAMEOF ( 'Customer'[City] ), 3, "Executive Report" ),
    ( "Country-Region", NAMEOF ( 'Customer'[Country-Region] ), 4, "Executive Report" )
}

However attempting to deploy the above table to an Analysis Services model with the “fields” column set as the Key column results in an error similar to this:

We can solve this by adding an arbitrary suffix to distinguish duplicate column or measure references in the “fields” column. This works because when Power BI translates the values in the “fields” column into columns or measures within a visual, it ignores any text following a valid fully-qualified column or measure reference. It can do this because any given fully-qualified column or measure reference in the form Table[Column or Measure] cannot be the prefix of another column or measure reference (that is, column/measure references have the prefix property).

Here is an Analysis Services-friendly version of the field parameter table where values of the “fields” columns in the highlighted rows have the suffix “_Exec” added.

Customer Parameter with Groups Analysis Services =
{
    ( "City", NAMEOF ( 'Customer'[City] ), 0, "Sales Report" ),
    ( "Country-Region", NAMEOF ( 'Customer'[Country-Region] ), 1, "Sales Report" ),
    ( "Postal Code", NAMEOF ( 'Customer'[Postal Code] ), 2, "Sales Report" ),
    ( "City", NAMEOF ( 'Customer'[City] ) & "_Exec", 3, "Executive Report" ),
    ( "Country-Region", NAMEOF ( 'Customer'[Country-Region] ) & "_Exec", 4, "Executive Report" )
}

Composite models

Power BI composite models with a DirectQuery connection to an Analysis Services model (2022+ or Azure Analysis Services) or Power BI model are currently unable to make use of field parameters defined in the remote model (see here). The field parameter table is visible when building reports but does not function as a field parameter table.

The only option at present is to define a field parameter table in the local model using either Power BI Desktop or Tabular Editor.

Conclusion

Personally, I was glad to discover that field parameters can be defined in Analysis Services models without too much trouble.

I hope the above Tabular Editor script and discussion of field parameters are useful! Please post any comments below or on social media.

In my next post I hope to summarize some interesting applications of field parameters that I presented to Power BI User Group Barcelona in a session titled Dissecting Field Parameters. Here is the recording 🙂

• Twitter
• LinkedIn