Filter embedded Power BI report using current user

[paulsnolan]

I'm new to PowerApps Portals and embedded Power BI reports. I've created a page which contains an embedded Power BI report and successfully filtered it using a hard coded value e.g. accounts/name eq 'ABC'. I'm now trying to filter using the account of the current user, user.parentcustomerid. I've tried using user.parentcustomerid and {{ user.parentcustomerid }} in place of 'ABC' but neither work. What is the correct syntax I should be using? Thanks you.

[UlrikkeAkerbæk]

Hello @paulsnolan.

The solution could be a filter on the Power BI embed. We have this in production to ensure that logged in user only see data from their own account, and it works great. 

We used the PowerBI Playground to generate a embed code with filter properties. Open the PowerBI Playground here: https://microsoft.github.io/PowerBI-JavaScript/demo/v2-demo/index.html. Choose "Code" from the top menu.

Replace the default values with the "Embed token", "Embed URL" and "Report ID" for your PowerBI Embed code. 

Choose "Interact" tab, open "Filters & slicers" and select "Set report filters" in the left menu.  

The "Code" section to the right will update with code for setting filters on the embed report. 

There is also a link to documentation on how filters work: https://github.com/Microsoft/PowerBI-JavaScript/wiki/Filters

In a web template I have this code where I use the filter: 

{% if user %}
<div id="reportContainer" style="width: 1400px; height: 824px;"></div>

<script>
$(document).ready(function () {
// store the embed token in variable
var getEmbedToken = "https://powerbi-prod-func.azurewebsites.net/api/xxxxxxxxxxxxxxxx";

$.ajax({
url: getEmbedToken,
jsonpCallback: 'callback',
contentType: 'application/javascript',
dataType: "jsonp",
success: function (json) {
var models = window['powerbi-client'].models;
const filter = {
$schema: "http://powerbi.com/product/schema#basic",
target: {
table: "Account",
column: "AccountID"
},
operator: "In",
values: ["{{user.parentcustomerid.id}}"]
};

var embedConfiguration = {
type: 'report',
id: json.ReportId,
embedUrl: json.EmbedUrl,
tokenType: models.TokenType.Embed,
accessToken: json.EmbedToken,
filters: [filter],
settings: {
navContentPaneEnabled: true,
filterPaneEnabled: false,
language: "no",
formatLocal: "es",
}
};

// Get a reference to the embedded report HTML element
var embedContainer = $('#reportContainer')[0];

// Embed the report and display it within the div container.
var report = powerbi.embed(embedContainer, embedConfiguration);
},
error: function () {
console.log("Error");
}
});
});
</script>
{% endif %}

I Include this web template into a web template associated with a web page.

Have a go and see if it works for you.

[paulsnolan]

Hi Ulrikke

Thank you for the reply, will try your suggestion

Paul

[OOlashyn · Accepted solution]

Hi @paulsnolan ,

There is an easy way to implement dynamic filtering using powerbi liquid tag and the ability to capture data into a variable via capture tag - we are using this in our portal for user data filtering

{% capture report_path%}https://app.powerbi.com/groups/00000000-0000-0000-0000-000000000000/reports/00000000-0000-0000-0000-000000000001/ReportSection01?filter=accounts/name eq '{{user.parentcustomerid.name}}'{% endcapture %}
{% powerbi authentication_type:"AAD" report_path%}

[paulsnolan]

Hi OOlashyn, thanks for the reply.

Should this code be placed in the filter section in the designer (under Advanced) or in the page details accessed from Portal Management. 

Regards

[OOlashyn]

Hi @paulsnolan ,

You can put it as part of your web template (check docs and example ) or put in Page Copy section of the Web Page (if you are using a web template that has Page Copy as part of it). Both of those actions are done in the Portal Management App.

[paulsnolan · Accepted solution]

Hi OOlashyn, thanks that worked!

[scooby1380]

Hi OOlashyn,

is this solution only applicable if the user is logged in with Azure AD credentials?

im looking to achieve something similar, however the portal users in this case are actually Contacts in a Dynamics 365 environment.

thanks

Scott

[OOlashyn]

Hi @scooby1380 ,

It will work with everything. This method is purely liquid based and has nothing to do with authentication type you selected for your powerbi report.

[Zap174]

Hi OOlashyn

If I wanted to filter by the current logged in user's email, is that possible? I want to filter the Power BI report based off of the current logged in user's email address.