0

I am evaluating Power BI as a possible tool for publication quality reports that will be distributed to clients. My source databases are in Microsoft SQL and Access.

I am somewhat confused by the Power Query Editor.

  • As a part of this, we will need to be able to specify the value of a Client Id field and apply a WHERE clause to the SQL and Access data source.

  • I see that I can filter data on one or more columns. This would be cumbersome if generating reports for a set of individual clients.

  • I see a Manage Parameters feature on the Home Tab of the Power Query Editor. Can these parameters be compared to values in database tables?

  • Are there examples of using M or DAX (or anything else) to implement an equivalent WHERE clause?

  • Do I have to run stored procedures, populate temporary tables and then run Power BI reports?

Doug Kimzey
  • 1,019
  • 2
  • 17
  • 32

1 Answers1

0

Here are a few options:

  1. Connect to your database using a Native Database Query. (Related post.)
  2. Connect to a view you create on your database that includes the WHERE clause.
  3. Filter the table in the Query Editor after connecting to it.
  4. Import or DirectQuery the whole table and filter at run time.

In #3, basic filtering usually gets folded into the under-the-hood query that Power BI sends to the database so that this is similar to #1 as far as your database sees.

With #4, it's possible to apply row-level security so that different people have access to different subsets of the data.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64