Forum Discussion
Dynamic M query parameters fail to load data
Hi Team,
I followed this article to use dynamic M query parameters. However, it fails to load data in the report. Could you help take a look? Thank you!
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
I created a parameter "ContainerParameter" and a table with "ContainerName", which is binded to the parameter. In another table, it takes the ContainerParameter and runs DirectQuery. I am able to select different parameter and view different result in the Query Editor, but it fails to load data in the report. The data source is "Azure Data Explorer (Kusto)". Thanks!
---
Here is the detail information:
Feedback Type:
Frown (Error)
Timestamp:
2022-12-12T08:14:22.1769163Z
Local Time:
2022-12-12T16:14:22.1769163+08:00
Session ID:
c99ec1fe-441f-44c2-ad8e-1818ff5d11b4
Release:
December 2022
Product Version:
2.112.582.0 (22.12) (x64)
Error Message:
DirectQuery error: DirectQuery may not be used with this data source - please consider moving to a supported data source or upgrading the SQL Server data source to the latest available version.
OS Version:
Microsoft Windows NT 10.0.22623.0 (x64 en-US)
CLR Version:
4.8 or later [Release Number = 533320]
Peak Virtual Memory:
2.07 TB
Private Memory:
0.994 GB
Peak Working Set:
1.3 GB
IE Version:
11.1.22621.0
User ID:
b2a14653-b221-4b0b-b2d6-68c4fb7e91a3
Workbook Package Info:
1* - en-US, Query Groups: 1, fastCombine: Disabled, runBackgroundAnalysis: True.
Telemetry Enabled:
True
Model Default Mode:
Composite
Model Version:
PowerBI_V3
Enabled Preview Features:
PBI_compositeModelsOverAS
PBI_b2bExternalDatasetSharing
PBI_enableWebView2
PBI_sparklines
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_horizontalFusion
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_optimizeTabRibbon
PBI_relationshipEditPane
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
MSIT
DPI Scale:
100%
Supported Services:
Power BI
I finally found the solution...
It seems like there is a bug of the DirectQuery with M query parameters. I named the DirectQuery in the Query Editor with some whitespaces, e.g., "My Direct Query". It worked after the whitespaces of the DirectQuery name is trimmed, e.g., "MyDirectQuery"...
6 Replies
I finally found the solution...
It seems like there is a bug of the DirectQuery with M query parameters. I named the DirectQuery in the Query Editor with some whitespaces, e.g., "My Direct Query". It worked after the whitespaces of the DirectQuery name is trimmed, e.g., "MyDirectQuery"...
Hi Shawn_Lin
Did you give it a try connecting to a SQL data source. Because looking on the error message it seems that Azure Data Explorer (Kusto) ist not supported by this feature.
Can you try connecting to an SQL database? If this wortks then you know its becuase your data source is not supported.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi Mikelytics ,
Thanks for your prompt response. Unfortunately, our data source is only from Kusto. Actually, we have another PowerBI dashboard that uses the same way to dynamically set the query parameters with Kusto data source. Sadly, we don't remember how we make it work, but it seems like this feature is supported for Kusto. I am wondering, if there is any setting I missed.
Thanks,
Shawn
Hi Shawn_Lin ,
hmm, Unfortunately I have not worked yet with this combination, It was only a best guess. Hope somebody else will be able to help. Sorry! 😕
true ... Now I am totally lost 😄
But referring to the error message is it maybe really the version of your data source? Are there different instances you connect to compared to the other usecase?
