Provide Field Parameter Support

[dm-p]

Field Parameters are now in preview in Power BI Desktop, and this seems to change the data view when a different field is selected. What this means is that you might design your spec with one field, but when selecting another, this is no longer visible, e.g.:

https://user-images.githubusercontent.com/10572054/168498184-e076632b-26e2-46b0-bbaf-ea5bc5cda6c0.mp4

Because Deneb's dataset is flexible, this means that we will need to find a way to manage this. Proposal is as follows:

• Expose a new data role called "Field Parameter", where this can be explicitly added, e.g.:

  

• Map this to a special field in the dataset, possibly named __parameter__.

• Add auto-complete for this new field (or fields)

• Add a signal, tentatively called pbiFieldParamName, which will provide the 'friendly' name of the column, which can then be used in expressions for axis titles, tooltips or other such purposes.

However

Because we can have multiple parameters in our report page, we may wish to add space for up to 3 so that folks can either:

• Add multiple parameters to a single data role to drill/expand on (As this seems to work if multi-select is applied to a parameter)
• Have independent control over either parameter (or all of them... it could be chaotically beautiful)

[dm-p]

Other things to think about would be:

• How we ensure that the right things go into the right place from a templating perspective when users add their fields to the visual.
• We may not need to care about this and let the user decide where their fields should go, like we currently do for data types or measures vs. columns.
• Maybe we should indicate that the field was provided from a parameter and its position in the data roles.
• We may also need to think about how many fields or measures are in each role, as adding multiple fields and expanding them will create additional columns in the data view. These may typically be concatenated in core visuals, if a column, but expanded/projected if they are measures.

There's so much to explore and figure out before we can standardise this...

[CSalcedoDataBI]

Excellent, thank you very more..

[vbakh]

For a while, I used a workaround for the Field Parameter (FP) issue in Deneb/Power BI that is based on the IsDefined Vega function as described there https://youtu.be/trKescjAQRI .

Here is another solution for that issue that doesn't require special processing within Deneb/Vega and has an extra positive side effect:

1. Create a FP as usual. For example, here is DAX code it generated in my test case:

   FPTest = {
   ("Country", NAMEOF('Data'[Country]), 0),
   ("Size", NAMEOF('Data'[Size]), 1)
   }

2. Add an extra column to the FP's calculated table via DAX and copy values from the first column to the newly created one (I also updated them to reflect that they will be used in Slice By / Legend scenario):

   FPTest = {
   ("Country", NAMEOF('Data'[Country]), 0, "By Country"),
   ("Size", NAMEOF('Data'[Size]), 1,"By Size")
   }    

3. Set all the values in the first column to the same constant string.

   FPTest = {
   ("Series", NAMEOF('Data'[Country]), 0, "By Country"),
   ("Series", NAMEOF('Data'[Size]), 1, "By Size")
   }

4. Rename that newly created column in Power BI from Value4 to something more appropriate for your case. Use that field in the FP slicer instead of the original one.

5. Add the original FP column to the list of Values for Deneb visual. It will be called "Series" there that won't change when you change selection in the slicer (By Country vs By Size):

When "By Country" is selected in the slicer:

When "By Size" is selected in the slicer:

There is another positive side effect from this workaround: new (fourth) column works properly in DAX measures with SELECTEDVALUE function.

[cristobalsalcedo]

Great solution, you have shared vbakh, I like this approach a lot because here I can keep the filter context unlike performing the transformations in vega-lite/ Vega, as you explain in the video...

Thank you for sharing this solution that I will definitely use it

[buddha200]

This is really a great workaround!

However I found that if you need in Deneb the FP plus any fields that are returned by the FP, the "true" original fields are renamed with the name given ("series" in your example) because it thinks all those have the same name. Then you cannot use them individually.

For ex. I have these fields at first, the 3 ones shown are the ones I need in the Deneb visual for presentation:

But I wanted to add another field, based on a FP, to let the user sort the data on the variance of his choice, so the FP would contains these 3 same fields:

Sort By Field = {
    ("SortByField", NAMEOF('_Measures'[Sls vs Bdg % Store]), 0, "By Score"),
    ("SortByField", NAMEOF('_Measures'[Compare To Variance]), 1, "By Selection"),
    ("SortByField", NAMEOF('_Measures'[Sls vs Bdg % Store POP]), 2, "By LY")
}

but then when adding my FP, the 3 original fields are renamed automatically by the visual:

and then on the data I have only one field, the one selected:

So FP is doing its job, but it seems I cannot use both the FP and the sames fields on the same visual. I could use the trick you mentionned on the video, or another Power BI measure with SWITCH to populate my Sorting field, but I wanted to take advantage of the FP, which is a nice feature.

I wanted to share my feedback on this really specific aspect, thank!

[dm-p]

With the latest visuals API (5.10.0), MS has introduced a sourceFieldParameters array as part of a field's metadata 🎉

If we implemented this API version today, this would be fine in the Power BI Service but only be supported in Power BI Desktop as of June 2024, so we have a little bit of time to work things out (as I like to ensure that we can use Deneb in at least the last three versions of Desktop).

But thinking about the implementation, if I have a parameter named Dynamic Category with three potential fields - [Country Code], [Segment], and [Product] - each selected field becomes part of the dataset, but each one will now have something like the following as part of its metadata:

[
  {
    type: { ... },
    displayName: 'Country Code',
    queryName: 'Demographic.Country Code',
    ...
    sourceFieldParameters: [
      {
        expr: { ... },
        displayName: 'Dynamic Category'
      }
    ],
    ...
  }
]

The critical part is that we now get the parameter name via sourceFieldParameters[n].displayName. This implies we could also have a column or measure in the dataset belonging to multiple field parameters. We also have to consider that we don't always single-select an entry in a field parameter, so if we have n fields selected (or none), then Power BI will send through multiple fields, which we currently observe in Deneb.

High-Level Proposal

To try to make everything track back to the field parameter rather than the component fields (therefore making it easier to template and handle field usage tracking, etc.), I believe that we aim to produce a field in the dataset that matches the name of the field parameter and consolidates everything under that field.

I think this would work fine if we could always guarantee that a single select is used in the report, but realistically, we should assume that 0-n fields can be supplied. It would then be up to the user to manage the parameter downstream in their spec.

Therefore, I think that we should treat field parameters like the scenarios in the flatten transform (Vega | Vega-Lite). What I mean by this is that instead of our data looking like it does currently:

row	Segment	Country Code	Product	$ Sales
0	Channel Partners	CA	Amarilla	2552747.28
1	Channel Partners	CA	Carratera	3122579.16
...	...	...	...	...

It would look like this:

row	Dynamic Category	Dynamic Category__names	$ Sales
0	[ 'Channel Partners', 'CA', 'Amarilla' ]	[ 'Segment', 'Country Code', 'Product' ]	2552747.28
1	[ 'Channel Partners', 'CA', 'Carratera' ]	[ 'Segment', 'Country Code', 'Product' ]	3122579.16
...	...	...	...

And if we had one category selected (e.g. Product):

row	Dynamic Category	Dynamic Category__names	$ Sales
0	[ 'Amarilla' ]	[ 'Product' ]	494439759.14
1	[ 'Carratera' ]	[ 'Product' ]	387411974.31
...	...	...	...

Using an array would also ensure that anywhere we use a text property without additional processing would defer to Vega or Vega-Lite's means of separating each entry onto a new line. This is similar to what we proposed with drilldown (#196). Keeping the granularity like this will also retain the way that flatten transforms consolidate fields if you specify multiple ones, e.g.:

{
  "type": "flatten",
  "fields": ["Dynamic Category", "Dynamic Category__names"],
  "as": [ "Value", "Field" ]
}

Would result in:

row	Value	Field	$ Sales
0	Channel Partners	Segment	2552747.28
0	CA	Country Code	2552747.28
0	Amarilla	Product	2552747.28
1	Channel Partners	Segment	3122579.16
1	CA	Country Code	3122579.16
1	Carratera	Product	3122579.16
...	...	...	...

As the original __row__ value is identifiable for each new row, this should still allow Power BI interactivity to work correctly.

Challenges To Solve

People with Existing Specifications

If we arbitrarily 'fix' field parameter behavior, we will break existing specifications that may be using them.

Proposal

I would suggest that we have a setting in Deneb that effectively specifies whether parameters should group component fields (like the above proposal) or not ('legacy' behavior).

This would be on by default for new specifications, but we would have it turned off for existing visuals. Ideally, we will only have this turned off if the dataset contains field parameters (which we could theoretically detect with the enhanced metadata). As part of the migration, we would ideally prompt the user that this is now a feature, but their existing configuration is still being used so that they can choose to migrate (or not) at their convenience.

Measures (or Specifically, Formatting String and Formatted Values)

Measures contain a bunch of extra metadata we need, such as the format string and the formatted value.

Proposal

By default, the raw values for measures would work just like they do for the above example, e.g., if we had [$ Sales] and [# Units Sold] both assigned to a field parameter:

row	Product	Dynamic Measure	Dynamic Measure__names	Dynamic Measure__format	Dynamic Measure__formatted
0	Amarilla	[ 494439759.14, 4733375 ]	[ '$ Sales', '# Units Sold' ]	[ '\$#,0;(\$#,0);\$#,0', #,0.00 ]	[ '$494,439,759', '4,733,375.00' ]
1	Carratera	[ 387411974.31, 4111179 ]	[ '$ Sales', '# Units Sold' ]	[ '\$#,0;(\$#,0);\$#,0', #,0.00 ]	[ '$494,439,759', '4,733,375.00' ]
...	...	...	...	...	...

Field Parameter May Be a Mixture of Columns and Measures

Yes, this is possible - there is nothing in the APIs that helps us to understand if the parameter is potentially 'mixed mode' or not, so dynamically determining this when the dataset is evaluated will potentially break the measure-based additional fields if they swap in and out between updates 😵‍💫

Proposal

This may be too hard to manage generically, so we should assume that all parameters can contain both. As such, we will have the following dataset fields for any parameter that is added:

• [Parameter Name]
• [Parameter_Name]__names
• [Parameter_Name]__format
• [Parameter_Name]__formatted

Columns or text values will pretty much look the same for the __formatted or have a null value for the __format columns (unless they are dates).

Field Tracking and Templating

Field parameters will have their special logic, so they should ideally be a recommendation when creating a sharable template.

Proposal

Templates use data types as a suggestion, but they will help users understand the author's intent when using with their own data. As such, we will use the Fluent UI TableCalculatorRegular icon, which is a 1:1 mapping with Power BI's data model iconography:

The dataset view in Deneb will look like the following example if a parameter is used:

Create or import new specification

Generate JSON Template

Edit specification field mapping

There Will Probably Be More

The above result from me thinking this through, but I'd recommend you also apply the idea to your existing use cases to see if you think it will fit. If it does, then great! If it doesn't, please provide some scenarios so that I can factor them into the final design.