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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Kudo Kingpin
Kudo Kingpin

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.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors