Pass parameters during retrieving the report PowerBI

[Anonymous]

Hi,

I was trying to find the answer to my question but the PowerBI develops quickly and maybe those topics are not up to date.

We wanted to use a Power BI in the case described below:

1. We create the report using the Direct Query. 

2. Users login to our application.

3. Our application calls for the report and sends parameter USER_ID.

3. We use this USER_ID parameter in Direct Query (to create SQL with condition: "where user_Id = USER_ID")

4. The user sees the report.

We can have thousands of users and we want to share them the same report template but filled with different data for every one of them. (using Direct Query with where clause). 

To get a link to the embedded report I call for endpoint /groups/{groupId}/reports/{reportId}.

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/getreport

and to be honest I think there is a good place to pass the parameters for the report we want to get.

Se we were trying the different options.

What we tried to do was:

1. Use Power BI parameters and update these parameters with REST API - fails, because we can have multiple users (different USER_ID) at one time.

2. Use the DAX's USERNAME() option in Direct Query but it fails too:

- https://community.powerbi.com/t5/Desktop/How-we-can-pass-dax-function-username-to-sql-query-query-to...

- https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13926666-provide-username-functio...

3. We can't use "roles" and filtering (it doesn't sound like a good solution).

To be honest, we don't want to force our users to have the account on Power BI (checking the 2nd option was only to find alternatives), so even if using this DAX'es USERNAME() works (Maybe is it possible in the current version?) then every user is forced to create/login on created account on Power BI.

So... it is possible to use Power BI Embedded with our case? 

[Anonymous]

HI @Anonymous,

I'd like to suggest add a user mapping table to map username and user_id and link to other tables with 'apply security filter in both direction' option.

Then you can configure RLS based on username on user mapping table username field and use the relationship to filter related table records.

After these steps, you only need to set your group workspace members to read permission and create a security group on office 365 side which used to assign RLS role and enabled the RLS filter based on current username and related user_id filed.

RLS with UserName()

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous 

In your solution, our clients are forced to have an account on PowerBI. 😞

I found another method:

1. Create a report with Direct Query with parameter (where userId = ...)

2. When user log to our app then CLONE that workspace to the new one using: https://powerbi.microsoft.com/pl-pl/blog/duplicate-workspaces-using-the-power-bi-rest-apis-a-step-by-step-tutorial/

3. Set that "userId" parameter in the cloned report.

4. Show the client that cloned report.

5. Remove the workspace (using Cron) after some time.

I made a simple app and step 1,2 is done but I feel that it could be a big hack. What do you think?

[Anonymous · Accepted solution]

Hi @Anonymous,

Yes, I think it will fail at step #3.
You can't set multiple current values of one query parameter at the same time on the Power bi service side. So these users will receive the report with contents of the last user id you configured.

For RLS with username, it will extract the current username as the parameter to filter records and apply to shared report contents if these users do not have edit permissions.

BTW, you can also parameterize your reports with query parameter as a template app then your users can get the app and use their owner user id to generate correspond apps contents.

What are Power BI template apps? 

Create a template app in Power BI 

Regards,

Xiaoxin Sheng

[Anonymous]

Any other ideas?