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 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:

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:

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:

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.