Using JavaScript For Power BI Filters in PBRS

One of the most useful features of PowerBI Reports is the ability to set report-level filters so that the information displayed within it can be relevant to the consumer.

What are Filters in Power BI?

Filters in Power BI distill and sort data and information based on a selected criteria. That is, you can select particular fields or values within fields and view only the information related to that. For instance, if you have a dataset related to sales of a store, you can use Filters to view a report having only the data for the selected aspects.

For example, if you only want to see the sales information in the Charlotte area in the year 2019, all you have to do is to put the filtering criterion as 2019 and Charlotte in their respective fields. The report of sales will immediately make changes accordingly and only show the graphs and visuals specific to sales statistics in Charlotte and in the year 2019.

How Does PBRS Support Report-Level Power BI Filters?

PBRS has always supported passing report-level Filters to Power BI reports so that when the report is automatically generated, it is delivered with just the right information. In fact, PBRS users love using Data-Driven subscription schedules to run multiple instances of Power BI reports, with filters configured via the data-driver and then delivered to data driven recipients, the resulting exported output file containing only the information relevant to the recipient.

One challenge that has always plagued the more advanced user, however, is when a Power BI report contains more than a couple of filters to configure. As the number of report filters increases, the process of setting up the PBRS schedule quickly becomes tedious and prone to errors.

In the September 2020 release of PBRS, we addressed this problem by allowing our advanced users to configure their Power BI Filters via JavaScript.

Using Javascript To Configure PBRS Filters

Instead of spending a long time manually entering a single filter at a time, PBRS users can now simply write the JavaScript function in their favorite code editor (or in PBRS itself) and then save it as part of the schedule. Furthermore, the JavaScript function can be stored in the end user’s database and pulled into the schedule using a data-driver insert.

How to Construct Power BI Filters With JavaScript

Microsoft has detailed information on how to create Filter objects in JavaScript and this can be found here:

https://github.com/Microsoft/PowerBI-JavaScript/wiki/Filters#contructingfilters

Once the filters have been created, the only requirement from PBRS is that it is placed inside a JavaScript function that returns an array of all the filters. An example of this can be seen below.


Function getFilters(){
let basicCustomerFilter = …..;
let advancedRegionFilter = ……;
let employeesFilter = …..;
return [basicCompanyFilter, advancedRegionFilter, employeesFilter]
}



In order to streamline the creation of the filter function for PBRS data-driven schedules, it is recommended to build a SQL function or Stored Procedure that accepts a key identifier and returns the full function. This is so that all the logic can be changed in one place and doesn’t have to be created in an inline SQL query.

For example:

SELECT dbo.Custumer.CustomerId, dbo.Customer.EmailAddress, dbo.GetPowerBiFilterFunction(Customer.CustomerId) AS PowerBiFilter FROM dbo.Customer

With this query as the data-driver, the PowerBi Filter column can simply be dropped into the Filter JavaScript field.

Let us know if you have any questions or suggestions about this feature.

Tutorial: Data Driven Subscription Scheduling for Power BI

Get Features PDFStart Free Trial