cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mike282
Helper II
Helper II

Getting errors when trying to use the new preview feature for Dynamic M query for Direct Query

Hi all so I'm trying to use the new preview feature for the Dynamic M query feature for direct query. I'm connected to a standard SQL database.

 

I've got two parameters created as datetime parameters. One for StartDate and one for EndDate. I then use the parameters in my SQL query for my direct query tables that I've created in Power Query. Again this is linked to a standard SQL database. I set some default dates for the StartDate and EndDate parameters just to make sure they work in my SQL query tables using Direct Query and they do work and can load fine.

 

image.png

 

I then loaded the data and then created two additional tables with date dimensions. One is called the StartDateTable and one is called the EndDateTable. The DAX is below as a example for the StartDateTable. It's exactly the same for the EndDate table:

 

 

StartDateTable = CALENDAR (DATE(2021,1,1), DATE(2023,12,31))

 

 

 

Afterwards I then bound the two date columns from my tables to the parameter:

image.png

 

This all works fine but when I started to add two single select slicers for the two date tables bound to the two parameters I'm getting the below error:

 

image.png

 

Why is this not working? Is it the data source? Like a standard SQL server does not work for Dynamic M queries? It works with the static default values I set for the paremeter but not when I bind a datetime table to the parameter?

 

Thanks for the quick reply.

 

So basically this new feature is about as useful as a fly/insect screen on a submarine....

 

 

EDIT:

 

So I created a new idea for the new feature. If forum members could please upvote this, that would be great. At this point this feature is basically useless. There's more data sources it doesn't support than ones it does and also some of the most common data sources most organisations use.

 

https://ideas.powerbi.com/ideas/idea/?ideaid=cd687a81-75b4-eb11-89ee-0003ff454c66

 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @Mike282 ,

Please check whether it hit any limitation in this offical documentation:

  • The feature is only supported for M based data sources. The following DirectQuery sources are not supported:

    • T-SQL based data sources: SQL Server, Azure SQL Database, Synapse SQL pools (such as Azure Synapse Analytics (formerly SQL Data Warehouse)), and Synapse SQL OnDemand pools
    • Live connect data sources: Azure Analysis Services, SQL Server Analysis Services, Power BI Datasets
    • Other unsupported data sources: Oracle, Teradata, and Relational SAP Hana, PostgreSQL
    • Partially supported through XMLA / TOM endpoint programmability: SAP BW and SAP Hana

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
yingyinr
Community Support
Community Support

Hi @Mike282 ,

Please check whether it hit any limitation in this offical documentation:

  • The feature is only supported for M based data sources. The following DirectQuery sources are not supported:

    • T-SQL based data sources: SQL Server, Azure SQL Database, Synapse SQL pools (such as Azure Synapse Analytics (formerly SQL Data Warehouse)), and Synapse SQL OnDemand pools
    • Live connect data sources: Azure Analysis Services, SQL Server Analysis Services, Power BI Datasets
    • Other unsupported data sources: Oracle, Teradata, and Relational SAP Hana, PostgreSQL
    • Partially supported through XMLA / TOM endpoint programmability: SAP BW and SAP Hana

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks for the quick reply.

 

So basically this new feature is about as useful as a fly/insect screen on a submarine....

 

 

EDIT:

 

So I created a new idea for the new feature. If forum members could please upvote this, that would be great. At this point this feature is basically useless. There's more data sources it doesn't support than ones it does and also some of the most common data sources most organisations use.

 

https://ideas.powerbi.com/ideas/idea/?ideaid=cd687a81-75b4-eb11-89ee-0003ff454c66

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors