by Steven Amani on Sep 24, 2020 12:06:40 PM
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.
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.
SELECT dbo.Custumer.CustomerId, dbo.Customer.EmailAddress, dbo.GetPowerBiFilterFunction(Customer.CustomerId) AS PowerBiFilter FROM dbo.Customer
Let us know if you have any questions or suggestions about this feature.