Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@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?
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.
@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?
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |