Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Titatovenaar2
Helper III
Helper III

Field parameters in local composite model with DirectQuery, unsupported?

Hello everyone,

 

We currently use field parameters in our local composite model with DirectQuery, which Microsoft states as being supported: 

You can't create parameters in live connection data sources without a local model. However, if you use DirectQuery for Power BI datasets and AS (preview), you can add a local model to create field parameters. For more information about using DirectQuery for live connections, see DirectQuery for Power BI datasets and AS (preview).

 

The reports we published to the cloud and deployed from Dev > Test > Prod all seemed to work perfectly fine, until all of the sudden on the Dev environment, the local composite model no longer allows for a refresh. We receive the following error:

Data source error{"error":{"code":"Premium_ASWL_Error","pbi.error":{"code":"Premium_ASWL_Error","parameters":{},"details":[{"code":"Premium_ASWL_Error_Details_Label","detail":{"type":1,"value":"Refresh is not supported for datasets with a calculated table or calculated column that depends on a table which references Analysis Services using DirectQuery."}}],"exceptionCulprit":1}}}

 
We have premium workspaces, and it says calculated tables aren't supported with DirectQuery. Since a field parameter is in fact a calculated table, it seems that field parameters are not supported in local composite models with DirectQuery, since I can't imagine that 'support' means that you can only use it in powerbi desktop, but you cannot publish and refresh.

 

If you look a bit further you also see this:
Announcing general availability for composite models on Power BI Datasets and Analysis Services mode...
In this article it says:

While during the preview you might not have received an error when using these sources, you will start seeing an error going forward as we tighten up all loose ends. Even if you do not see an error, you should still use this feature with supported sources. The unsupported sources are documented, so please make sure you use supported sources for your reports that rely on this feature now.

 

In other words: it worked nicely for a while, but it never really had support to begin with? If that's the case, why does Microsoft state you can add Field parameters in the local composite model, while you are unable to refresh it in the cloud and therefore not be able to share it with users. Basically, it is only supported for developers while you stay inside of PowerBI Desktop? 

Can someone shed more information on this?

Or maybe a workaround? I have tried this:
https://github.com/TabularEditor/TabularEditor3/issues/541

 

But I don't see the values of the measures selected, but only the names of the measures for both the parameter measure and parameter measure field names... Code used:

      {
        "name": "Parameter",
        "lineageTag": "1ac06833-769c-40c2-a3f8-746c77751f35",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Parameter",
            "dataType": "string",
            "isNameInferred": true,
            "sourceColumn": "[Value1]",
            "sortByColumn": "Parameter Order",
            "lineageTag": "d2389e1b-c104-42f2-99a7-db71062d7d4e",
            "relatedColumnDetails": {
              "groupByColumns": [
                {
                  "groupingColumn": "Parameter Fields"
                }
              ]
            }
          },
          {
            "type": "calculatedTableColumn",
            "name": "Parameter Fields",
            "dataType": "string",
            "isNameInferred": false,
            "isHidden": true,
            "sourceColumn": "[Value2]",
            "sortByColumn": "Parameter Order",
            "lineageTag": "701b9001-5380-414b-b45d-a961b7fde8d0",
            "extendedProperties": [
              {
                "type": "json",
                "name": "ParameterMetadata",
                "value": {
                  "version": 3,
                  "kind": 2
                }
              }
            ]
          },
          {
            "type": "calculatedTableColumn",
            "name": "Parameter Order",
            "dataType": "string",
            "isNameInferred": false,
            "isHidden": true,
            "sourceColumn": "[Value3]",
            "lineageTag": "5de1d910-1fa9-4bea-9288-acd4ba90b7fe"
          }
        ],
        "partitions": [
          {
            "name": "Parameter",
            "mode": "import",
            "source": {
              "type": "calculated",
              "expression": [
                "{",
                "    (\"Omzet\", NAMEOF('Fact'[Omzet]), 0),",
                "    (\"Omzet (%)\", NAMEOF('Fact'[Omzet (%)]), 1),",
                "    (\"Omzet RY\", NAMEOF('Fact'[Omzet RY]), 2)",
                "}"
              ]
            }
          }
        ]
      }

Is it possible to use this code in main model to work with DirectQuery?

Kind regards,

Igor

5 REPLIES 5
BarnabasToth
Resolver I
Resolver I

Hi @Titatovenaar2 and @joaoribeiro,

 

The case maybe was different in the past, but as of now Field parameters in your local composite models are supported to be refreshed in the Service.

On the other hand calculated columns and tables referencing content from the remote model are not supported by design (security reasons: evaluation of those objects happens before RLS gets applied).

 

Please mark this as a solution if it addressed your question.

 

Regards,

Barna

joaoribeiro
Impactful Individual
Impactful Individual

Hi @Titatovenaar2 ,

 

Unfortunately Field Parameters are not supported in Composite Mode with Direct Query, only for Live Connection. My team has been suffering with this issue for a while. Possible workarounds are recreating the field parameters in the composite mode, which can be bad for maintenance, or use calculation groups to achieve the same result, as they are supported in Direct Query.

 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!
Best regards,
Joao Ribeiro

Thanks for confirming and the fast reply. I think I will try the calculation group route with a switch measure to recreate the field parameter functionality inside the main model and make it hidden for the users.

Anonymous
Not applicable

Were you able to get something to work? I think I am having a similar issue with field parameters not allowing refresh in the powerBI service. I am using field parameters along with a drowndown selection to allow users to change data the visuals are showing based on the drop down selection. Not sure if calculation groups can be used in place in my case. I will have to look into that.

@Titatovenaar2  glad to help. Wish you success in the project 🙂 If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.