Running Your Own MDX And DAX Queries In Power BI Desktop

Every time there’s a new release of Power Query or Power BI Desktop, I always check to see if there are any interesting new M functions that have been added (I used #shared to do this, as detailed here). For the RTM version of Power BI Desktop I spotted two new functions:


As well as ODBC connections, we can now use OLEDB and ADO.NET data sources – although they aren’t shown in the UI yet. And you know what this means… with an OLEDB connection we can now run our own MDX and DAX queries against SSAS data sources! I assume this will be coming in Power Query in Excel soon too.

Here’s an example query showing how to use OleDB.Query() to run an MDX query against the Adventure Works DW cube in SSAS Multidimesional:

    Source = OleDb.Query(
              "Provider=MSOLAP.5;Data Source=localhost;
               Initial Catalog=Adventure Works DW 2008", 
              "select {measures.[internet sales amount]} on 0, 
               [date].[calendar].[calendar year].members on 1 
               from [adventure works]"

As you can see, it’s pretty straightforward: you just need to supply a connection string and a query. You will need to tell Power BI Desktop which credentials to use when running the query the first time you connect to SSAS, and that’s probably going to be Windows:


You will also see a prompt the first time you run the query, asking for permission to run a Native Database Query:


This prompt will appear each time a different MDX query is run; you can turn off this prompt in the Options dialog on the Security tab by unchecking the Require user approval for new native database queries box:


Here’s the output of the MDX query from the example code:


16 responses

  1. Pingback: daily 07/29/2015 | Cshonea's Blog

  2. Pingback: POWER BI Desktop

  3. This is it!


    Thank you for the information.

    Hmm the next question is, can i load power bi results in excel – maybe with excel power query?

    • Now it is possible to connect to power bi from Excel.
      Using the Power Bi Publisher for Excel add-in you can generate a connection string.

      Some modifications are required:
      1) REMOVE all the DOUBLE QUOTES from the connection string that Excel generates for you
      2) only the following properties have to be retained to make this work:
      the “…” need to be taken from the Excel generated connection string
      Initial Catalog=…;
      Data Source=pbiazure://;
      Identity Provider=…

      To write the dax query downloading DAX studio is a good idea

  4. Chris, is it possible to create a DAX calculation within PowerBI against a data source connection to an MDX/SSAS Cube? Seems writing DAX in PBI only works against a Direct Query or other kinds of data sources but not against an MDX cube.

  5. MDX is not working properly in PowerQuery. It ignores or missinterprets “union” statements – instead of union – it makes another column.

  6. I don’t have Adventure works, so I’m posting example from our cube – you should replace server, cube, etc..:

    Source = OleDb.Query(
    “Provider=MSOLAP.5;Data Source=YourSSASserver;Initial Catalog=CubeName”,

    } on columns,
    {except([Date].[week_id].children,[Date].[week_id].&) } *

    } on rows
    from [CubeName]”

    • This isn’t a Power Query problem exactly, it’s due to Power Query requesting a flattened rowset instead of a cellset. SSRS does the same.

  7. I once tried to extract data from a published power bi model using a DAX query in Excel, but i power query was unable to retain the login credentials. has this been fixed recently?
    my workaround was to create a simple pivot table with a measure i could use to drill through to some details. This action would create a new connection to the datamodel with a DAX query in there to retrieve the first 1000 rows. This query could be replaced by the data you wanted.
    Still had to write a macro to refresh the connections, because these tables do not refresh when you click Data > Refresh All … Microsoft says it was purposfully designed this way.

    So doing this directly in PQ is much better… it worked, but the credentials expired soon and so it didnt work as a future proof solution … only for quick ad hoc queries

Leave a Reply to Chris Webb Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: