Need to pass a custom UserId in via javascript and out via datasource api URL

[Anonymous]

I’m not sure if this is possible. I’m quite new to Power BI, have done a lot of research on the forums and on Stack Overflow, but am still not sure if what I want is possible.

We have a current report that is loaded into an iframe on a public web page using a connection to Sql Server tables as the data source. We need to change this so that a filter is applied to the data based on the user who is logged in to our web app. This means the report would differ for each user. (Ths user is our app user, not the Sql user)

What I want to do is change the datasource so that it calls an API in our app, which then returns Json data. Our API would need to be passed the UserId as a parameter so it can filter the results by user before returning data.

To make this work, I would first need to pass the UserId into Power BI via the javascript we’re currently using to load the report. The javascript looks like this:

config = {
        type: 'report',
        tokenType: models.TokenType.Embed,
        accessToken: '@Model.EmbedToken.Token',
        embedUrl: '@Html.Raw(Model.EmbedUrl)',
        id: '@Model.Id',
        permissions: models.Permissions.All,
        settings: {
            filterPaneEnabled: false,
            navContentPaneEnabled: true,
            viewMode: 'view'
        }
    };

    report = powerbi.embed(reportContainer, config);

Is this possible? Is there a way to pass an extra custom parameter into Power BI via javascript and then for Power BI to pass that paramater back to our API as part of datasource URL?

[v-jiascu-msft]

Hi @Anonymous,

There is a solution for your scenario. Please refer to embedded-row-level-security.

Best Regards,

Dale

[Anonymous]

This might work for us, though our use case is far more complex than the example shown. We'll still need to build the dataset for each user on our end before passing it back to the report.

The CustomData and User Identity mentioned, can we access this and attach it to our api URL in the Advanced Editor?

[v-jiascu-msft]

Hi @Anonymous,

1. The CustomData feature will only work for models that reside in Azure Analysis Services, and it only works in live mode. Please refer to embedded-row-level-security#working-with-analysis-services-live-connections.

2. Do you mean "Advanced Editor" is the one in the Desktop? 

3. It's parameters that we use in Power BI to get data. Please refer to deep-dive-into-query-parameters-and-power-bi-templates/.

I think we need RLS in your scenario rather than parameters. 

Best Regards,

Dale

[Anonymous]

We have 1,000s of users. Each user has a different view of the data. That view is not based on simple access to columns or rows of data.

For example: some of the data we display is accumulated totals based on 100s of different pieces of data. For each user, the 100s of pieces of data that go into building the totals will be different. Accumulating these totals is not a simple addition process, it factors in many other calculations.

This is why I think the only way to do this is for us to build the dataset for a user dynamically via our own api, returning a unique json result set for each user.

RLS looks far too simple for what we require.

The Advanced Editor I was referring to was in the Desktop UI, where datasources are configured. We’d like to send back the UserId as part of the datasource url. Ex: https://myapi/datasource?userId= 121345

My key question is if we can attach the UserId I pass in as part of the datasource url as above.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

As far as I know, I'm afraid we can't pass something like UserId here through the Embedded URL. Actually, it won't work even if we can do it. Because we just interact with the dataset and report in Power BI Service. We don't interact with the data source directly. We can't trigger a dataset refresh when embedding. (It's the Import mode we are talking here). 

Best Regards,

Dale