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

[Mike282]

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.

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:

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:

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

[v-yiruan-msft · Accepted solution]

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

[Mike282]

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