-1

I've migrated SSAS Tabular Model to work in DirectQuery mode.
Everything works fine besides DetailRows expression.

No matters what DAX expression I set for Detail Rows Expression, drillthrough action in Excel fails with the same error: MDX/SQL operations are not supported in DirectQuery mode

Any ideas?

ZENIT
  • 500
  • 2
  • 4
  • 16

1 Answers1

-1

This is a limitation of the technology.

See documentation on page 6

DirectQuery limitations There are a few very important design considerations if you are planning to use DirectQuery:

• MDX queries are not supported for a model in DirectQuery mode. This means you cannot use Microsoft Excel or Microsoft PerformancePoint as reporting clients on top of a DirectQuery-only model, because these clients all issue MDX queries.

Frostytheswimmer
  • 720
  • 4
  • 19
  • Such a limitation doesn't appear in SSAS 2017 documentation - [DirectQuery mode restrictions](https://learn.microsoft.com/en-us/sql/analysis-services/tabular-models/directquery-mode-ssas-tabular?view=sql-server-2017#bkmk_prereq) – ZENIT Jun 26 '19 at 12:07
  • Ok, I'll double down with the link provided. There are many things provided in the DAX and MDX section that could be the problem. "No user-defined hierarchies. No native SQL queries. When querying a tabular model in DirectQuery mode, Analysis Services converts DAX formulas and measure definitions into SQL statements. DAX formulas containing elements that cannot be converted into SQL syntax will return validation errors on the model." – Frostytheswimmer Jun 26 '19 at 15:03
  • You've not provided any information about the Version of Excel, SSAS or SQL server. Your setup is not clear. Are there more than a million rows? There are may be unsupported DAX keywords in your measures that are unsupported.. – Frostytheswimmer Jun 26 '19 at 15:03