cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trevorgermain Regular Visitor
Regular Visitor

Power BI Embedded - DirectQuery dynamic parameters

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Power BI Embedded - DirectQuery dynamic parameters


@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.

Capture.PNG

2 REPLIES 2
Moderator Eric_Zhang
Moderator

Re: Power BI Embedded - DirectQuery dynamic parameters


@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.

Capture.PNG

trevorgermain Regular Visitor
Regular Visitor

Re: Power BI Embedded - DirectQuery dynamic parameters

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 106 members 1,479 guests
Please welcome our newest community members: