March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
90 | |
83 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |