One of the vital helpful options of PowerBI Experiences is the flexibility to set report-level filters in order that the data displayed inside it may be related to the buyer.

What are Filters in Energy BI?

Filters in Energy BI distill and kind knowledge and knowledge based mostly on a particular standards. That’s, you may choose explicit fields or values inside fields and think about solely the data associated to that. For example, you probably have a dataset associated to gross sales of a retailer, you should use Filters to view a report having solely the info for the chosen facets.

For instance, when you solely need to see the gross sales data within the Charlotte space within the yr 2019, all it’s important to do is to place the filtering criterion as 2019 and Charlotte of their respective fields. The report of gross sales will instantly make adjustments accordingly and solely present the graphs and visuals particular to gross sales statistics in Charlotte and within the yr 2019.

How Does PBRS Assist Report-Degree Energy BI Filters?

PBRS has at all times supported passing report-level Filters to Energy BI reviews in order that when the report is mechanically generated, it’s delivered with simply the fitting data. The truth is, PBRS customers love utilizing Knowledge-Pushed subscription schedules to run a number of cases of Energy BI reviews, with filters configured through the data-driver after which delivered to knowledge pushed recipients, the ensuing exported output file containing solely the data related to the recipient.

One problem that has at all times plagued the extra superior person, nonetheless, is when a Energy BI report incorporates greater than a few filters to configure. Because the variety of report filters will increase, the method of establishing the PBRS schedule rapidly turns into tedious and vulnerable to errors.

Within the September 2020 launch of PBRS, we addressed this downside by permitting our superior customers to configure their Energy BI Filters through JavaScript.

Utilizing Javascript To Configure PBRS Filters

As a substitute of spending a very long time manually coming into a single filter at a time, PBRS customers can now merely write the JavaScript perform of their favourite code editor (or in PBRS itself) after which put it aside as a part of the schedule. Moreover, the JavaScript perform could be saved ultimately person’s database and pulled into the schedule utilizing a data-driver insert.

Tips on how to Assemble Energy BI Filters With JavaScript

Microsoft has detailed data on tips on how to create Filter objects in JavaScript and this may be discovered right here:

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

As soon as the filters have been created, the one requirement from PBRS is that it’s positioned inside a JavaScript perform that returns an array of all of the filters. An instance of this may be seen under.

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

With the intention to streamline the creation of the filter perform for PBRS data-driven schedules, it is strongly recommended to construct a SQL perform or Saved Process that accepts a key identifier and returns the total perform. That is so that every one the logic could be modified in a single place and doesn’t must be created in an inline SQL question.

For instance:

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

With this question because the data-driver, the PowerBi Filter column can merely be dropped into the Filter JavaScript subject.

Tell us you probably have any questions or strategies about this function.

Tutorial: Knowledge Pushed Subscription Scheduling for Energy BI

Get Options PDFBegin Free Trial