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 problem with showing any report by its ID (GUID).

But there are some reports that need to be parameterized, for instance, with the 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 a 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? 

Answer 1

Firstly, filter needs to be defined in the report, so that the user can set it manually. There are two ways to pass parameters to Power BI report from an external source

• In Power BI Application

We can mention filter by setting filter parameter in the report URL in the address bar. The parameter takes a custom filter query:

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

• "12345678-6418-4b47-ac7c-f8ac7791a0a7" is report id.

• "Store" is a dataset.

•  PostalCode is a parameter to filter out.

• "eq" is an equality operator.

After encoding the URL, it looks in this way:

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

• JavaScript sendMessage oDataFilter parameter

JavaScript controls the loaded BI report by postMessage() calls with parameters. There is an extra option oDataFilter that can be set to filter the report.

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

Here is the following code:

function onFrameLoaded() {

    var m = {

        action: "loadReport",

        reportId: reportId,

        accessToken: accessToken,

        oDataFilter: "Store/PostalCode eq '15012'"

    };

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

}

Note: There should not be any dots in the filter parameters (data source or parameter name) as the Power BI code rejects it silently as invalid names.

If you are preparing for the Power BI certification exam, then take up this Power BI online training by Intellipaat that offers instructor-led training, hands-on projects, and certification.