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
