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

18 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.”

  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

  4. Using parameters in PowerBI desktop seems to work for Import and Direct Mode but after deploying your report to PBI Service it seems that the parameters used in the DirectQuery are converted to strings and will not be recognized as parameters any more.

  5. Can we write back data from power bi to the database . For instance lets say I have a slider to select one of the parameter and after selection I get min and max value . Can this value be written back in a database .
    Could you please let me know the feasibility .

    Thank you in Advance

  6. you can include PowerApps in PowerBI. This has the advantage that you can provide values from your PowerBI report where you have made a selection bases on your slider / filter settings to your PowerApps.

    • true, but thats a different scenario
      even with PowerApps you cannot change parameters in PowerBI which would have impact on the Direct Query connection which is the idea of blog post

  7. Hi

    I have tried the same exact setup as you and I get the following error message when I try to apply query changes:

    “Connecting to tables from more than one database isn’t supported in DirectQuery Mode”

    so in other words I can’t simply ignore the warning message regarding import, since it won’t let me apply the query changes.

    I am using Power BI Report Server (May 2020) version. Not sure if this could have something to do with it?

    • As PBI Report Server uses older builds of what the regular Power BI uses, it may be that this is not supported with PBI RS

      Opening the .pbix in my regular PBI Desktop still works just fine

  8. Hi
    Thanks for your post!
    In fact, my question is this solution can be a substitution for passing parameters like what we have in a ssrs reports for sql server data source.

    • partially
      It may work “OK” in PBI Desktop where you can easily change the parameters but is not feasible once the report is deployed to the PBI service where you need to change the parameters on the dataset.
      did you have a look at paginated reports? thats basically the SSRS integration in PowerBI

      -gerhard

Leave a Reply

Your email address will not be published. Required fields are marked *

*