Embed your Power BI report with predefined filters

I have recently gotten this questions a few times so time to dust off my coding skills Surprised smile and get to work … the key question in both cases is how can I make sure I filter my report before any queries are being send to the underlying data source.

When using Power BI embedded you can use Javascript to pass in a filter instead of using the Power BI UI. To test how this worked I installed the sample app from GitHub: https://github.com/Microsoft/PowerBI-Developer-Samples and then uploaded a report to my Power BI that uses a on prem AS model. I use that so I can profile the queries easily and show you how it works. The same would apply for any data source.

Here is the embedded report that currently shows all colors:
image

The DAX query send to my SSAS is the following:

EVALUATE
TOPN(
     1002,
     SUMMARIZECOLUMNS(
                      'DimProduct'[ColorName],
                       "Sum_of_SalesAmount", 'FactOnlineSales'[Sum of SalesAmount]
                    ),
    [Sum_of_SalesAmount],
    0,
    'DimProduct'[ColorName],
    1
)
ORDER BY
[Sum_of_SalesAmount] DESC, 'DimProduct'[ColorName]

Now lets filter it on load. Let’s look at filter method 1 (and the one you see most typical): https://github.com/Microsoft/PowerBI-JavaScript/wiki/Filters.

This JavaScript method uses the report loaded event to see if the report is loaded and then push in the filter into the report:

const filter = {
        $schema: "http://powerbi.com/product/schema#basic",
        target: {
            table: "DimProduct",
            column: "ColorName"
        },
        operator: "In",
        values: ["Silver"]
    };
var config = {
              type: 'report',
              tokenType: models.TokenType.Embed,
              accessToken: accessToken,
              embedUrl: embedUrl,
              id: embedReportId,
              permissions: models.Permissions.All,
              settings: {
                            filterPaneEnabled: true,
                            navContentPaneEnabled: true
              }
};
// Embed the report and display it within the div container.
var report = powerbi.embed(reportContainer, config);
report.on('loaded', event => {
        report.getFilters()
            .then(filters => {
                filters.push(filter);
                return report.setFilters(filters);
            });

When we run this we do see the report is getting filtered:
image

Then I see 2 queries being send:

EVALUATE
TOPN(
     1002,
     SUMMARIZECOLUMNS(
                      'DimProduct'[ColorName],
                      "Sum_of_SalesAmount", 'FactOnlineSales'[Sum of SalesAmount]
                    ),
                    [Sum_of_SalesAmount],
                    0,
                    'DimProduct'[ColorName],
                    1
)
ORDER BY
[Sum_of_SalesAmount] DESC, 'DimProduct'[ColorName]

And

DEFINE VAR __DS0FilterTable =
FILTER(KEEPFILTERS(VALUES('DimProduct'[ColorName])), 'DimProduct'[ColorName] = "Silver")
EVALUATE
TOPN(
1002,
SUMMARIZECOLUMNS(
                 'DimProduct'[ColorName],
                  __DS0FilterTable,
                  "Sum_of_SalesAmount", 'FactOnlineSales'[Sum of SalesAmount]
               ),
               [Sum_of_SalesAmount],
               0,
               'DimProduct'[ColorName],
               1
)
ORDER BY
[Sum_of_SalesAmount] DESC, 'DimProduct'[ColorName]

That is not what we want, we want to send only the last one. But this is to be expected, if we again look at the JavaScript we see that we are only pushing the new filter AFTER the ‘loaded’  event on the report has been triggered.  Now this is great in the case where you want to write code that pushes in filters dynamically from another app but not if you want to filter the page already filtered.

Now there is another option and that that allows you to set a particular configuration on report load (and not AFTER).  So I change my embed config to include filters as described here: https://github.com/Microsoft/PowerBI-JavaScript/wiki/Embed-Configuration-Details

const filter = {
        $schema: "http://powerbi.com/product/schema#basic",
        target: {
            table: "DimProduct",
            column: "ColorName"
        },
        operator: "In",
        values: ["Silver"]
    };
var config = {
              type: 'report',
              tokenType: models.TokenType.Embed,
              accessToken: accessToken,
              embedUrl: embedUrl,
              id: embedReportId,
              permissions: models.Permissions.All,
              filters: [filter],
              settings: {
                            filterPaneEnabled: true,
                            navContentPaneEnabled: true
              }
};
// Embed the report and display it within the div container.
var report = powerbi.embed(reportContainer, config);

Observe that the filters parameter is an arrary so you can pass in multiple filters if you want.

This now shows us the right report:

image

And sends only the one query:

DEFINE VAR __DS0FilterTable =
FILTER(KEEPFILTERS(VALUES('DimProduct'[ColorName])), 'DimProduct'[ColorName] = "Silver")
EVALUATE
TOPN(
     1002,
     SUMMARIZECOLUMNS(
                      'DimProduct'[ColorName],
                      __DS0FilterTable,
                      "Sum_of_SalesAmount", 'FactOnlineSales'[Sum of SalesAmount]
     ),
     [Sum_of_SalesAmount],
     0,
     'DimProduct'[ColorName],
1
)
ORDER BY
[Sum_of_SalesAmount] DESC, 'DimProduct'[ColorName]

 

That shows the 2 ways of setting filters in Power BI Embedded, one for each scenario. The one thing I want to mention here is that the filters are placed as part of the JavaScript and thus CANNOT be used as a security feature as anyone can change it.

5 Replies to “Embed your Power BI report with predefined filters

  1. Hi Kasper,
    How would you handle a case, when you have multiple users and you want only to show them their data in reports? It should be done on the server site, but is there a way to do it?

  2. Hi Kasper,

    Hi, i’ve working in a power bi report and i have to embedded it in a web page, but this webpage has the same filter pane as the report, i wanna know if it possible to connect the filter pane from the website to the filter panel in power bi, so if i select an filter option in the web page’s panel it also change in the report

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.