topic Power BI Embedded - DirectQuery dynamic parameters in Developer

Power BI Embedded - DirectQuery dynamic parameters

trevorgermain — Fri, 17 Nov 2017 19:38:20 GMT

I would like to be able to embed Power BI report, and be able to do a DirectQuery against my datasource based on the currently logged in user.

The only way I've been able to get this to work is to use the Username function in DAX, but by the time DAX is available, I'd have already had to load WAY too much data (not relevent data) into my model.

Can I filter at the PowerQuery?

Re: Power BI Embedded - DirectQuery dynamic parameters

Eric_Zhang — Mon, 20 Nov 2017 06:41:30 GMT

@trevorgermain wrote:

I would like to be able to embed Power BI report, and be able to do a DirectQuery against my datasource based on the currently logged in user.

The only way I've been able to get this to work is to use the Username function in DAX, but by the time DAX is available, I'd have already had to load WAY too much data (not relevent data) into my model.

Can I filter at the PowerQuery?

@trevorgermain

That is the only approach possible I'm aware of. As to "load too much data", you may don't worry about that. Because in DirectQuery mode, it doesn't load all data from database and then apply a filter. Instead, Power BI composes a proper SQL with the filter and send the SQL to database. Eg. in my test, I capture the below in my test with SQL Server profiler, the WHERE clause is appended due to USERNAME() filter.

Re: Power BI Embedded - DirectQuery dynamic parameters

trevorgermain — Mon, 20 Nov 2017 16:03:32 GMT

I've been mostly using Import mode, and have a new requirement where DirectQuery may make the most sense. Thank you for your response. I tried the exact thing you suggested and am seeing proper filtering on the query in SQL Profiler.