Passing parameters to Power BI filter programmatically

Question

In my application I'm displaying a Power BI report. It already works, so there's no problems with showing any report by its ID (guid).

But there are some reports that need to be parametrized, for instance, with current year or person who views the report. That's my question: how to do it?

To be more specific, I'm embedding the report inside HTML <iframe> element. I set iframe URL to an URL received from report definition's embedUrl (received from REST API). I'm controlling it by JavaScript code that calls postMessage().

Report definition:

{
  "id":"12345678-6418-4b47-ac7c-f8ac7791a0aa",
  "name":"Retail Analysis Sample",
  "webUrl":"https://app.powerbi.com/reports/12345678-6418-4b47-ac7c-f8ac7791a0aa",
  "embedUrl":"https://app.powerbi.com/reportEmbed?reportId=12345678-6418-4b47-ac7c-f8ac7791a0aa"
}

JavaScript code to loads the report:

function onFrameLoaded() {
    var m = {
        action: "loadReport",
        reportId: reportId,
        accessToken: accessToken
    };

    iframe.contentWindow.postMessage(JSON.stringify(m), "*");
}

Now I feed to filter the report by a parameter from my custom application. Is there a way to send or pass a value to filter dataset in the report?

How it could be a duplicate of a question asked LATER and with different concern? — andrew.fox

andrew.fox andrew.fox · Accepted Answer · 2016-02-23T07:39:32

First of all, filter has to be defined in the report, so user can set it manually.

There are two possible ways to pass parameters (thus set filter) to the Power BI report from external source.

a) In Power BI Application

You can specify the filter by setting filter parameter in the report URL (in browser address bar). Parameter takes custom filter query:

https://app.powerbi.com/groups/me/reports/12345678-6418-4b47-ac7c-f8ac7791a0a7?filter=Store/PostalCode eq '15012'

where "12345678-6418-4b47-ac7c-f8ac7791a0a7" is a report id, and "Store" is a dataset, and PostalCode is a parameter to be filter out. "eq" is a equality operator.

URL should be encoded, so final url looks like this:

https://app.powerbi.com/groups/me/reports/12345678-6418-4b47-ac7c-f8ac7791a0a7?filter=Store/PostalCode%20eq%20%2715012%27

b) JavaScript sendMessage oDataFilter parameter

JavaScript (browser client side) controls the loaded BI report by postMessage() calls with parameters (just like in the question above). There is an extra option oDataFilter that can be set to filter the report.

Set it like this: oDataFilter: "Store/PostalCode eq '15012'"

Full code would look like this:

function onFrameLoaded() {
    var m = {
        action: "loadReport",
        reportId: reportId,
        accessToken: accessToken,
        oDataFilter: "Store/PostalCode eq '15012'"
    };

    iframe.contentWindow.postMessage(JSON.stringify(m), "*");
}

Remarks

There must not be any dots in the filter parameters (datasource or parameter name) as the Power BI code rejects it silently as invalid names;

Passing parameters to Power BI filter programmatically

2 Answers