Using Power BI Desktop Direct Query with Parameters

I frequently work on projects where we have multiple tiers on which our solution is deployed to using continuous integration / continuous deployment (CI / CD) pipelines in Azure DevOps. Once everything is deployed, you also need to monitor these different environments and check the status of the data or ETL pipelines. My tool of choice is usually Power BI desktop as it allows me to connect to e.g. SQL databases very easily. However, I always ended up creating a multiple Power BI files – one for each environment.

Having multiple files results in a lot of overhead when it comes to maintenance and also managing these files. Fortunately, I came across this little trick when I was investigating in composite models and aggregations that I am going to explain in this blog post.

To be honest, I barely used Power BI Direct Query in past and so maybe this feature has been there for quite some time without me realizing it but It may also be that it was introduced just recently with composite models.
So the “feature” is, that you can also use Query Parameters to parameterize your Direct Query queries. This is pretty awesome if you think of it for a second:

  • easy switching between databases
  • use one file for all environments
  • only maintain a single file
  • no need to import/load any data
Power BI DirectQuery with Parameters

The configuration within Power Query is also quite easy – simply replacing the hard coded values with the ones from the parameters:

Power Query configuration using Parameters instead of hard-coded values

And that’s it already! you can now easily switch between different databases by just using Power BI parameters and the Direct Query connection will change automatically to the new server/database.
Of course, all the target servers/databases have to have the same schema otherwise, you will get an error.

Caveats:
Even though this looks quite trivial, there are some caveats which makes me believe this is not fully supported yet. You may noticed above already that in Power Query, when going to the step that actually queries the database, it complains about that this step would cause the whole table to be converted to Import Mode. However, you can just ignore it and go on with the next step to remain in Direct Query Mode.

Ignore warning and DO NOT convert to Import Mode

It seems like Power BI keeps track from where a table was originally imported. So if you want to add a new table, make sure to copy an existing Direct Query table and change it accordingly instead of going to “New Source > …” !

Also, you need to make sure that you have entered the credentials for the different source databases at least once – otherwise Power BI will ask you when you query the database the first time. This is also the reason why this does not work so well in the Power BI service as changing the parameters there is not as simple as it is in Power BI desktop.

As I said, I do not know if this is a new feature (or a feature at all), but it is definitely helpful for certain scenarios.

Downloads:
Power BI Workbook: DirectQuery_wParameters.pbix

5 Replies to “Using Power BI Desktop Direct Query with Parameters”

  1. Pingback: Parameterized Direct Query In Power BI – Curated SQL

  2. The file you provided couldn’t be open in my Power BI Desktop:
    Version: 2.68.5432.841 64-bit (April 2019)

    Have you succeeded in changing parameter values at Power BI Service? I haven’t. Each time I go to Parameters section I get the following message:

    “Parameters haven’t been defined for this dataset yet. If you want to set parameters, use the Query Editor.”

    • regarding opening the file locally: as for every Direct Query file, it tries to connect to the data source and this fails in your case as you probably do not have you SQL server running at localhost\V2017 and/or do not have a database called AdventureWorksDW2012 or AdventureWorksDW2014
      You can try changing this after ignoring the initial error message and fill in your values in the parameters

      I have not played with the PBI service and this approach yet. In my case I was using a local SQL Server. Deploying this to the service would make it much more complicated. If you have an Azure SQL DB instead, this would make sense

  3. Hi

    DirectQuery with parameters is great as an idea but the real obstacle is
    that you cannot populate parameters using filters and criteria from the
    PBI report.

    So the typical reporting scenario where you need to pass parameters to
    the database to get back results is simply not possible.

    Is there any workaround for that ?

    Thanks

    • As of now you cannot change parameters from the report canvas but I totally agree that would be really useful.

      Maybe they add this feature in the future

      -gerhard

Leave a Reply