cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Filter embedded Power BI report using current user

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions

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%}

 

 

----------------------------------------------------
If you find this post helpful consider marking it as a solution to help others find it.

View solution in original post

Hi OOlashyn, thanks that worked!

View solution in original post

8 REPLIES 8
Advocate I
Advocate I

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.

 

UlrikkeAkerbæk_1-1601531401155.png

 

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.  

UlrikkeAkerbæk_2-1601531542896.png

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.

Hi Ulrikke

Thank you for the reply, will try your suggestion

Paul

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%}

 

 

----------------------------------------------------
If you find this post helpful consider marking it as a solution to help others find it.

View solution in original post

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

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.

----------------------------------------------------
If you find this post helpful consider marking it as a solution to help others find it.

Hi OOlashyn, thanks that worked!

View solution in original post

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

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.

----------------------------------------------------
If you find this post helpful consider marking it as a solution to help others find it.

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Users online (18,147)