Method to use RLS (Multiple Parameters) with Power BI Embedded

[kushanNa]

I was searching for how to pass parameters to an embedded report and found the following great video by Adam from "Guy in a Cube."

https://www.youtube.com/watch?v=yNF-_l2f7w0&t=98s

The method described works well for a single parameter, but if you have multiple parameters, you might encounter issues.

Here is a simple solution I found for it:

• Create a text parameter
• Give a default current value by joining both parameters with a comma

next, write a M code to split these parameter and into two parameters

then use the resulting variables as your parameters in your direct query

create a new table with one column with all merged multiple parameters and bind that column with main parameter(param) you created (you need to apply RLS to this column)

include the merged parameters to your C# code

after that , follow the steps in the video and That’s it! This should resolve the issue.

Additionally, there are a few missing steps from the video that I’d like to share might help you:

• You need to enter the client ID and secret on the C# secrets page before debugging.

• The video uses the old playground interface. In the new playground, you need to click on the Developer Sandbox page and select "Use my embedded token."

Hope this helps!

[Mahhin_Shahzad1]

Hey @kushanNa  , 

Thank you for the article. I'm trying to implement it but am quite confused about some things. Sorry if these are basic questions - this is my first time working with RLS and Power query .

My scenario is that I want to filter based on company ID and forecast ID. Each user will have these details passed with the effective identity and I don't want any slicer (chicklet ) etc on my report level to change the Paramter at Power Query Level .

I have a few questions:

1. How does the RLS filter get passed down to Power Query?
   I don't see any details about the RLS filtering passing down to Power Query. In Power Query, these are just 2 default parameters we created and have split them. ChatGPT kept insisting that these would just get picked up at the Power Query level by themselves without any kind of intervention, which has made me even more confused.
2. About the M code splitting query You said we need to write an M code to split these parameters into two parameters. Do we add that as a blank query? I created the query and I'm seeing the result as a query result, but how do these become parameters we can use?
   
3. Roles? In this approach, would we be required to create any roles? I think one role with something like "CompanyId = UserPrincipal()" and "ForecastId = UserPrincipal()" should be enough and everything else would work dynamically, right?

I'm on a deadline, so if you can get back to me quickly it would help a lot. Thank you!

[kushanNa]

Hi @Mahhin_Shahzad1,

In my scenario:

1. How does the RLS filter get passed down to Power Query?
   Create a new table with one column that merges all multiple parameters, and bind that column to the main parameter you created. (You need to apply RLS to this column.) , if you are new to parameter binding and passing it to power query you can check this video, he explain it nicely with an example https://www.youtube.com/watch?v=pXU_SpiBWUA&t=14s&ab_channel=DataPlatformCentral 

   Since I believe you're not manually selecting a value (like in the above video), you'll need a Chiclet Slicer for this. The Chiclet Slicer visual is responsible for pushing the selected value into the parameter, which then gets passed into Power Query.

   In the General settings of the Chiclet Slicer, enable "Force Selection" and uncheck "Multiple Selection." Then hide the slicer behind another visual . this video explain how to use chiclet silcer for using Row Level Security / UserPrincipalName with Dynamic Query Parameters https://www.youtube.com/watch?v=vyR0uAbLdTk&ab_channel=DapperDash 

2. About the M code / splitting the query
   No need to write a new query — you can add your logic at the top of your existing M code, right after the let keyword.

   and do not create two parameters only one needed param, because you can't pass two parameters for C# code you onely pass one, that's why we combine both using commar "," and do the splitting inside the code (paramater1,parameter2 yours compid,forcastid) the query will split it into two compid and forecast paramters and use it in power query

   

3. Roles?
   Yes, you need to create a role using the UserPrincipalName() function — same function used in Adam’s video & also in the 2nd video i have attached 

[tharunkumarRTK]

 @kushanNa
I think you are not asking any question but presenting a usecase. I would suggest you to post this as a community blog but not as a post. 🙂

Thank you for sharing this info, I have question if you can answer then that would be great. I understand that you are using dynamic m query parameters and dynamic RLS functionality, incase if I assume you linked your RLS mapping table with the import mode table that you created by appending the parameters. The effective identity that you have passed from the C# code can filter the import mode (parameters) table because of RLS, but how will it change the M Query parameter value it will remain same unless some report user selects the value from a slicer or any visual? 

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!

[kushanNa]

Okay, maybe I'm trying to understand the latter part of your question here. So, you are asking how the value changes on a parameter in a scenario where someone is not selecting the slicer value ?

To achieve that, you will need to use the Chiclet Slicer visual. Use it as a slicer instead of the traditional slicer. In the General settings of the Chiclet Slicer, change it to "Force Selection." & untick multiple selection . This will automatically select the value from the column and pass the parameter to the parameters.

[tharunkumarRTK]

Okay, This solves the requirement. Thanks for sharing.

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!