Forum Discussion
Pass parameter to power query
Hi folks, I'm interesting in how to pass parameter from report e.g. username() DAX to power query and filters the SQL sentence?, I know it exists RLS, but the semantic model have many dim tables. π Example: SELECT * FROM TABLE WHERE IDUSER=username() .
thanks in advance.
To pass parameter values dynamically, then you need to use dynamic M - Query parameters. This functionality requires your storage mode to be in. direct query mode. More information here: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
Based on my understanding from your requirement, all your dimension tables contains 'UserId' filed and you want your report to be filtered based on the 'userid' of the user who is comsuming the report. I want to give two suggestions, depending on your storage mode you can decide one
1. Import Mode - Create a table with all IDs of your users and link (relationship) this table with all your dimension tables and implement RLS on UserId column of this table. This is called dynamic RLS functionality and it propagates userid filter to your dimension tables. More info here: https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple
2. Direct Query - Ask your data engineer to create RLS policy in your database for all your dimension tables, After that you can leverage RLS in the source in Power BI. More infor here: https://www.youtube.com/watch?v=WyvfZMaXLBs
One last option which I can suggest you is dynamic m query parameters, and keep all your dimension tables in direct query mode. More info here: https://www.youtube.com/watch?v=vyR0uAbLdTk
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
2 Replies
Thanks for your insights, @tharunkumarRTK.
USERNAME() function in DAX returns the username of the logged-in user, Peter_23 .
This filter simply means that the logged-in user will only see his/her records in the whole data set. For example, the username field in the Sales Rep table is defined as the usernames of Power BI accounts. And transactions table is also related to this table based on Sales Rep ID. So filtering one table will affect others. As a result, this single-line filter will enable dynamic row-level security in the whole Power BI solution here.
This blog post explains in detail how RLS is deployed and how it works. My previous example is also based on the tables of the blog. Please refer to
Dynamic Row Level Security with Power BI Made Simple - RADACAD
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
To pass parameter values dynamically, then you need to use dynamic M - Query parameters. This functionality requires your storage mode to be in. direct query mode. More information here: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
Based on my understanding from your requirement, all your dimension tables contains 'UserId' filed and you want your report to be filtered based on the 'userid' of the user who is comsuming the report. I want to give two suggestions, depending on your storage mode you can decide one
1. Import Mode - Create a table with all IDs of your users and link (relationship) this table with all your dimension tables and implement RLS on UserId column of this table. This is called dynamic RLS functionality and it propagates userid filter to your dimension tables. More info here: https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple
2. Direct Query - Ask your data engineer to create RLS policy in your database for all your dimension tables, After that you can leverage RLS in the source in Power BI. More infor here: https://www.youtube.com/watch?v=WyvfZMaXLBs
One last option which I can suggest you is dynamic m query parameters, and keep all your dimension tables in direct query mode. More info here: https://www.youtube.com/watch?v=vyR0uAbLdTk
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
