Does anyone know if field parameters are on the Fabric roadmap?

[arpost]

Greetings, all. I've been REALLY feeling the lack of the ability to use field parameters/calculated columns/tables in Fabric when working with a semantic model using DirectLake. Does anyone know if that is on the roadmap or if there's been discussion about when support will be added there?

[cpwebb · Accepted solution]

Sorry not be clear. To answer your questions:

>>Can you clarify what you mean by "field parameters and calculated tables with no dependencies on other data elsewhere in the model"? 

If you have a calculated table whose definition is FILTER('MyTable', 'MyTable'[MyColumn]="X") then that calculated table has a dependency on another table in the model called 'MyTable'. However, if you have a calculated table whose definition is GenerateSeries(1,5) then it has no dependencies on any other table in the model. So in your examples a measure table is an example of the second type of calculated table.

>>Tabular Editor wouldn't (and really shouldn't need to) be an option here as it requires a paid plan for use with Fabric and the PBI Service.

Agreed, and the native Microsoft tooling will catch up soon. But it's not true you need the paid version of Tabular Editor to work with Fabric: I just created a calculated table in a Direct Lake model using Tabular Editor 2 (the free version) and it worked.

Thanks for providing those examples. It's what I was expecting you to say, but to build a case for supporting all calculated columns and calculated tables in Direct Lake mode then I need real customer feedback.

Chris

[arpost]

Got it. That's helpful. I think many of my calculated tables are independent typically.

Great to hear regarding MS tooling, and that's also good to know about Tabular Editor! I think I mainly went by the fact that Fabric is listed under the Enterprise tier for pricing for Tabular 3. I'll have to check that out. Well, that and figure out how to create field parameters using Tabular Editor.

You're very welcome. As I thought about all of this, what I'd especially love to see come to Fabric/PBI would be:

1. Built-in support for a "measure table": Houses all measures and supports multi-level display folders.
2. Native "option set" or "value set" functionality: This would enable BI developers to define cached lists of values and reference them for entire report pages, in visuals, and in measures using friendly names (similar to the example below with Dataverse/Power Apps Choice sets).

Similar concept to field parameters but data/value-oriented rather than being focused on specific fields/measures. I achieve #2 currently by creating a table, populating it with values, and then mapping the values to measures, but it isn't perfect and requires manual key mappings:

This would be far less "hacky" if this were all available OOB. Fabric's advent makes this especially significant since I envision more models are going to shift from Import mode to DQ or DL.

[v-gchenna-msft]

Hello @arpost 

I just want to check if your queries were answered by @cpwebb ?
Otherwise, will respond back with the more details and we will try to help .

Thank you,
Chenna Gopi Krishna

[v-gchenna-msft]

Hello @arpost ,

We haven’t heard from you on the last response and was just checking back to see  if your queries were answered by @cpwebb ?
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread .

Thank you,
Chenna Gopi Krishna

[arpost]

He did help clarify some things, and I felt like I was able to give feedback, so I'll close this thread out.

[cpwebb]

Field parameters and calculated tables with no dependencies on other data elsewhere in the model are already supported I think (or will be soon), although you'll need to use Tabular Editor or a similar tool to create them. Calculated columns or calculated tables that are based on data elsewhere in the model are not on the roadmap at the moment but we have heard a lot of feedback on this topic.

I'm pretty sure I know what you're going to say, but can you explain why you need to use calculated columns in Direct Lake mode? Why can't you add the columns you need further upstream, for example in a Dataflow? The better the arguments you can give for this, the more likely the work needed to enable this will be prioritised.

Chris

[arpost]

Greetings, @cpwebb (side-note: thanks for all the stuff you post on your blog; has been immensely informative through the years). That is good to know. Can you clarify what you mean by "field parameters and calculated tables with no dependencies on other data elsewhere in the model"? I ask because I thought most/all field parameters would be using data from elsewhere in the model.

Tabular Editor wouldn't (and really shouldn't need to) be an option here as it requires a paid plan for use with Fabric and the PBI Service. I've also been hesitant to consider a non-MS solution with Fabric given issues I've heard with XMLA changes to the Service. I am not seeing field parameters listed in the semantic model UI in Fabric, but I'll keep an eye out.

On the Use of Calculated Tables/Columns

Regarding your question on calculated columns, there are several reasons where calculated tables/columns would be in view:

1. Adding dynamic fields to data
2. Creating option sets/select lists for calcs. and slicers
3. Creating measure tables

#1: Adding Dynamic Fields to Data

We have dynamic text value fields we need to add on top of data coming out of our Fabric DW that it may not make sense to persist in the DB (balance buckets, status indicators, etc.). This is being done in a SQL view, but I know Fabric switches to using DirectQuery (DQ) rather than DirectLake (DL) when working with views, which I assume is less performant.

If views won't be supported for DL, then I have hopes that DL support for calculated columns or tables would make it possible to add the fields without sacrificing DL performance.

#2: Creating Option Sets/Select Lists for Calcs and Slicers

I often have need of “option sets” or “pick lists” for use as filters and slicers to provide dynamic inputs in reports. These take the form of disconnected tables. Normally, I'd create a few calculated tables using DATATABLE in DAX; these may use calculated columns as well, but they don't always.

In the example below, I have two disconnected tables with the values for the slicers shown and a third data table:

In Fabric, the only way to achieve this is to:

1. Create a table in the DW containing select list values.
2. Create a view in the DW filtering values to Option Set #1.
3. Create a view in the DW filtering values to Option Set #2.
4. Add Option Set views to Semantic Data Model.
5. Refresh data

While this can work, it takes a bit more effort and also requires depending on views...which circles back to performance discussion in #1. I'll add that this could be partially solved if Fabric allowed you to add duplicates of the same tables/views to a semantic model, but the Fabric Model UI only lets you check/uncheck DW artifacts.

#3: Creating a Measure Table

Because measures are required to have a home table in Power BI (home tables are the bane of my BI existence), if ever you delete a table or switch models, reports connected to Fabric semantic models break. Even if the names are the same, they break if the home tables don't match. The measures themselves can't be accessed report-side so you can rehome them, and visuals with dependencies on those measures break. If you use measures in visual properties, you can't even troubleshoot.

Thus, having a dedicated Measures table is needed to ensure you don't lose all your measures. Plus, it just makes it easier to track all your measures when you have 100+ measures in a model. As it stands in Fabric, the only way I've found to solve this is to create a "dummy" table in the DW and house measures there, which seems rather funny to have to create a useless DB table just to support BI behavior.

Hope this helps. Let me know if you have any questions.

[v-gchenna-msft]

Hello @arpost ,

Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.