Power Platform Community Forum Thread Details

Filter power bi report in power pages site on accountnumber

Hello experts,

I have a power bi report in my portal. I want to filter the report on Accountnumber. So when a person is logged-in he only sees the data that is connected to the Account he is assigned to.

Can someone help me out with this?

Thank you

Categories:

General

Hi samuel10 ,

Use Row-Level Security (RLS) in Power BI combined with Power Pages security. Here’s how you can set this up:

Open your Power BI report in Power BI Desktop. Go to the Modeling tab and select Manage roles. Create a new role (e.g., "AccountFilter"). Apply a DAX filter on the table containing account data. For example: [AccountNumber] = USERPRINCIPALNAME() Publish the Report to Power BI Service:

Embed the Report in Power Pages: Add the Power BI report to your Power Pages site using the Power BI Embedded component or an iframe with an embed link. Use the Power Pages Design Studio or custom HTML/JavaScript to embed the report.

Enable Single Sign-On (SSO): Configure SSO between Power BI and your portal using Azure AD authentication. This ensures the logged-in user's identity is passed to Power BI for RLS.

In Power Pages, fetch the user's assigned AccountNumber using Liquid templates or JavaScript. {% assign accountNumber = user.accountnumber %}

Use the Power BI JavaScript API to pass the AccountNumber dynamically to the report. let models = window['powerbi-client'].models;
let config = {
type: 'report',
embedUrl: 'https://app.powerbi.com/...yourReportEmbedUrl...',
accessToken: 'yourAccessToken',
settings: {
filterPaneEnabled: false,
navContentPaneEnabled: false
}
};

// Apply filter on AccountNumber
let accountFilter = {
$schema: "http://powerbi.com/product/schema#basic",
target: {
table: "YourTableName",
column: "AccountNumber"
},
operator: "In",
values: ["{{ accountNumber }}"]
};

let reportContainer = document.getElementById('reportContainer');
let report = powerbi.embed(reportContainer, config);

report.on("loaded", function() {
report.setFilters([accountFilter]);
});

try out

Quick Links