none
Power Query, meta data, OPENQUERY and DAX RRS feed

  • Question

  • I have a Power Query query that calls a view that uses OPENQUERY in SQL server to call a DAX query on a tabular model.

    Power Query however executes the view multiple times in order to get some metadata before running the actual view for data results.

    The problem is that the OPENQUERY contained in the view has to return all rows from the DAX query, it cannot just get 100 or a 1000 rows.

    This has a performance impact.

    How can I resolve or work around this

    One solution would be to have tabular model as a direct source in Power Query and not having to go through OPENQUERY


    Sign here

    Tuesday, September 15, 2015 8:43 AM

Answers

All replies

  • You can run DAX queries directly against a Tabular model in the latest version of Power Query. It's not visible in the UI, but you can use the OleDb.Query() function to do this. The following blog post has more details (it's written for Power BI Desktop, but I've tested a DAX query in Power Query in Excel and it works the same):

    http://blog.crossjoin.co.uk/2015/07/29/running-your-own-mdx-and-dax-queries-in-power-bi-desktop/

    HTH,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    • Marked as answer by Pieter Olivier Tuesday, September 15, 2015 12:52 PM
    Tuesday, September 15, 2015 12:06 PM
  • I saw it became available in Power BI Desktop and forgot all about it for the Excel Power Query release!

    Thanks Chris, this works !!!!


    Sign here

    Tuesday, September 15, 2015 12:54 PM