Feature Request: Add ability to Add a Field Parameter table

[edhans]

This is a new May 2022 desktop feature, and it apparently goes beyond the DAX with a number properties that need to be set in order for Tabular to recognize it.

A full thread of the details can be found at https://twitter.com/markbdi/status/1526602566486081536?s=20&t=1Z2Ny-GTJlSeKocTxneX3g

Would be nice if we could right-click on Table in the TOM, and create a new Field Parameter table, with a bit of assist on the DAX (one or two rows of example code would be sufficient I think, no need initially for a full UI like PBID has) with all underlying properties set correctly.

[otykier]

While I agree this should be a built-in feature, the short term solution is the following script (tested and works in both TE2 and TE3!):

// Before running the script, select the measures 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).
var name = "Parameter";

if(Selected.Columns.Count == 0 && Selected.Measures.Count == 0) throw new Exception("No columns or measures selected!");

// Construct the DAX for the calculated table based on the current selection:
var objects = Selected.Columns.Any() ? Selected.Columns.Cast<ITabularTableObject>() : Selected.Measures;
var dax = "{\n    " + string.Join(",\n    ", objects.Select((c,i) => string.Format("(\"{0}\", NAMEOF('{1}'[{0}]), {2})", 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 te2 = table.Columns.Count == 0;
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;

if(!te2) {
    // Rename the columns that were added automatically in TE3:
    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
nameColumn.SortByColumn = orderColumn;
nameColumn.GroupByColumns.Add(fieldColumn);
fieldColumn.SortByColumn = orderColumn;
fieldColumn.SetExtendedProperty("ParameterMetadata", "{\"version\":3,\"kind\":2}", ExtendedPropertyType.Json);
fieldColumn.IsHidden = true;
orderColumn.IsHidden = true;

[pramod-mafls]

Thank you for this. How do we rename a column we are bringing in the parameters??

[edhans]

for the field you want to rename, set the "Name Inferred" property to FALSE, save, then rename the field.

[dpesante]

Thanks for providing this... what about updating an existing field parameter? What would be the code to drop/add a field parameter table or is there a way to "update" and existing FP?

[sshoj]

There is a System.NullReferenceException error on line 36 (GroupByColumn) when model doesn't have the groupbycolumn field
Is these anyway to avoid that?

[TheoJoore]

I get the same error. Is there a solution for this issue?

[otykier]

@sshoj and @TheoJoore what is the compatibility level and mode of your models? The GroupByColumns property is only available for Power BI models at CL 1400 or above.

[TheoJoore]

@otykier , thnx for you reply. I want to implement this in an Azure Analysis Services model with compatability level 1500. If I set it local to for example 1510 the error in Tabular Editor is gone, but then when I deploy it to the Azure Analysis Server I get this message

[otykier]

As I said above, this only works for Power BI models - not SSAS / AzureAS models.

Microsoft documentation for GroupByColumns:

This metadata object is only supported at database compatibility level of 1400 or above for Pbi server.

[klelund]

@otykier Any info on when it will be available for SSAS / AzureAS models?

[TheoJoore]

So to be clear: there is no option to add a field parameter to an AzureAS model?

[otykier]

Correct. You can, however, use the DirectQuery over AS feature in Power BI, to build a Power BI model based on an AzureAS database, to which you can then add a field parameter: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters#limitations

Currently you can't create parameters in live connection data sources, without a local model. However, using DirectQuery for Power BI datasets and AS (preview), you can add a local model to create field parameters. Read more about DirectQuery for Power BI datasets and AS (preview).

[luukhendrikscordstrap]

---

While I agree this should be a built-in feature, the short term solution is the following script (tested and works in both TE2 and TE3!):

// Before running the script, select the measures 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).
var name = "Parameter";

if(Selected.Columns.Count == 0 && Selected.Measures.Count == 0) throw new Exception("No columns or measures selected!");

// Construct the DAX for the calculated table based on the current selection:
var objects = Selected.Columns.Any() ? Selected.Columns.Cast<ITabularTableObject>() : Selected.Measures;
var dax = "{\n    " + string.Join(",\n    ", objects.Select((c,i) => string.Format("(\"{0}\", NAMEOF('{1}'[{0}]), {2})", 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 te2 = table.Columns.Count == 0;
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;

if(!te2) {
    // Rename the columns that were added automatically in TE3:
    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
nameColumn.SortByColumn = orderColumn;
nameColumn.GroupByColumns.Add(fieldColumn);
fieldColumn.SortByColumn = orderColumn;
fieldColumn.SetExtendedProperty("ParameterMetadata", "{\"version\":3,\"kind\":2}", ExtendedPropertyType.Json);
fieldColumn.IsHidden = true;
orderColumn.IsHidden = true;

This code generated a correct looking calculated table that wouldnt compile for its life. In the end the markup was the problem. After removing any spaces or tabs generated by the code and making the markup myself the calculated table worked instantly. Would still like this as a feature in TE3!

[mark-endicott]

While I agree this should be a built-in feature, the short term solution is the following script (tested and works in both TE2 and TE3!):

// Before running the script, select the measures 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).
var name = "Parameter";

if(Selected.Columns.Count == 0 && Selected.Measures.Count == 0) throw new Exception("No columns or measures selected!");

// Construct the DAX for the calculated table based on the current selection:
var objects = Selected.Columns.Any() ? Selected.Columns.Cast<ITabularTableObject>() : Selected.Measures;
var dax = "{\n    " + string.Join(",\n    ", objects.Select((c,i) => string.Format("(\"{0}\", NAMEOF('{1}'[{0}]), {2})", 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 te2 = table.Columns.Count == 0;
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;

if(!te2) {
    // Rename the columns that were added automatically in TE3:
    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
nameColumn.SortByColumn = orderColumn;
nameColumn.GroupByColumns.Add(fieldColumn);
fieldColumn.SortByColumn = orderColumn;
fieldColumn.SetExtendedProperty("ParameterMetadata", "{\"version\":3,\"kind\":2}", ExtendedPropertyType.Json);
fieldColumn.IsHidden = true;
orderColumn.IsHidden = true;

@otykier - this would be great if in TE2 you could select columns from multiple tables when holding CTRL, I can select many columns from one table, or many tables, but not columns from different tables. What am I missing??

[JanBoldt11]

Is the #C still the way to do this or have it been added as functionality to the TE. User would like to be able to add it as local measure in the data model, level 1570.

[mlonsk]

Hi @JanBoldt11
Sorry for the late reply, The C# script is still the way to handle this.

[hareesh591]

Hello @otykier ,

Good day !!

I'm using Tabular Editor 2.21.1 version to create field parameters for my direct lake semantic model. I'm following the C# code to create field parameters which you have provided. While running the C# script, I'm unable to select columns from multiple tables even though the tables have relationship with each other. Please let me know if I need to enable any settings in Tabular editor or the creation of field parameters is not available in free versions of Tabular editor.

Looking forward for your response.

Thanks
Hareesh Devarakonda

[otykier]

Hi @hareesh591
Just to provide a bit more context: When you create a calculated table, Tabular Editor 2 cannot automatically infer what the resulting columns on that table are (i.e. column names and data types), since this depends entirely on the DAX expression, and you need a DAX parser to get that information. Tabular Editor 2 does not have a DAX parser built in (but Tabular Editor 3 does have that).

However, with Tabular Editor 2, you should be able to Save (Ctrl+S) the model after adding the DAX expression. This will then cause the Power BI engine to update the columns on the calculated table, and afterwards you should be able to see the columns in Tabular Editor.

[mlonsk]

Hi @hareesh591
What I would personally do is select columns from a single table, run the C# script, and then replace the table DAX Expression with the DAX you have from Power BI Desktop.

I have tried to show the process here:

Hope this helps

[hareesh591]

Hi @mlonsk ,

Wonderful. This process picked up columns from different tables as expected but unfortunately I'm getting below error when dragged the field parameter column into my work area. Have you faced the similar issue? If yes, it could be great if you provide me the solution.

[edhans]

A field parameter is a calculated table and needs to be calculated, or it is empty. Just right-click it and Calculate it in TE/3, or go to the Model at the top of the TOM, right-click and Refresh - Recalculate the model.

[edhans]

For tabular editor 2, this page of scripts should help with the recalculation. https://www.elegantbi.com/post/datarefreshintabulareditor

[TinyTimothy2]

But none of this works with SSAS models. Unclear why.

[otykier]

This is a documented limitation: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters#:~:text=you%20can't%20create%20parameters%20in%20live%20connection%20data%20sources%20without%20a%20local%20model.

[klelund]

Any knowledge about whether this limitation is likely to change in the future (field parameters available in ssas) ?