Configure Power BI slicers and filters in BI Helper
Last updated: June 19, 2023
This tutorial details slicer and filter configuration in BI Helper using the Retail Sales Report in Power BI as an example.
The Retail Sales Report has two slicers, District Manager and Period, which we will apply to generate user-specific PDFs and email them to the respective users.
The detailed steps to configure slicers and filters in BI Helper are given below.
- Create your BI Helper account at https://portal.bihelper.tech.
2. After signing in, you will go to the Job Summary tab, where you will see the Retail Sales Report, which is a pre-configured demo job. Click on the Edit button (blue pen) of the Retail Sales Report.
3. This takes you to the Schedule Report tab. Verify that the job name is Retail Sales Report. Save and go to the Generate PDF tab.
4. The Generate PDF tab has three sections. In the Account Authorization section, sign in to your Power BI account with your Microsoft credentials and ensure that you select the Retail Sales Report.
Next, go to the Apply Slicers, Filters and Email IDs section. Select the manual input mode. In the manual mode, enter the slicer / filter details in the table based on the instructions in the A. Slicer Configuration and B. Filter Configuration sections below.
To generate PDFs without applying filters or slicers, leave the Slicer / Filter Name and Slicer / Filter Value columns blank.
A. Slicer Configuration
a. BI Helper reads slicers set up on the first page of a Power BI report. Slicer names must follow the format: Slicer Title<>Table Name/Column Name. BI Helper identifies slicers using the Title field in Visualizations > General in Power BI. BI Helper does not read the slicer Header in Power BI.
b. Defining the District Manager slicer: In the image below, District Manager slicer uses the DM column in the Store table in the Power BI data model. In the BI Helper input table, the slicer is named District Manager<>Store/DM. Slicer values are the names of the District Managers — Tina Lassila, Andrew Ma and so on.
c. Defining the Period slicer: The Period slicer uses the Report Month column in the Dates Master table. So it is to be named as Period<>Dates Master/Report Month. Date values are to be defined in the ISO 8601 format (e.g., 2022–08–01T00:00:00) and reporting period in the StartDate :: EndDate format. E.g., for the reporting period August 1 — 31 August 2022, the Period slicer value is to be defined as 2022–08–01T00:00:00 :: 2022–08–31T00:00:00.
d. Numeric Slicers: Use the int(12345) format to enter values of numeric slicers.
e. Multiple slicers are separated using the pipe operator (|). To apply both slicers, enter the Slicer / Filter Name in the BI Helper input table as Period<>Dates Master/Report Month | District Manager<>Store/DM. Enter a corresponding Slicer / Filter Value as 2022–08–01T00:00:00 :: 2022–08–31T00:00:00 | Chris McGurk. Add a row for every slicer combination for which a PDF report is to be generated.
Important: Slicer title, table name, column name and slicer values are all case-sensitive and have to be entered exactly as they appear in the Power BI report.
f. Enter the email IDs of the report recipients in the To, CC and BCC columns. Multiple email IDs in a column are to be separated by a semicolon (;).
For automated slicer / filter and email input, refer to our tutorial at https://medium.com/p/bfebd39d4c84.
B. Filter Configuration
BI Helper supports report level filters set up on the first page of a Power BI report. It does not support page level filters or filters applied to individual visuals. BI Helper identifies filters using the Title field in Visualizations > General in Power BI. BI Helper does not read the filter Header in Power BI.
If your report uses filters instead of slicers, the above steps remain unchanged except for the way the filter names are defined. Filter names follow the format Table Name/Column Name. Filter names and values need to be entered in the same columns as the slicer names and values.
Important: Table name, column name and filter values are all case-sensitive and need to be entered exactly as per the Power BI report.
If your report uses both slicers and filters, you can separate them using the pipe operator (|).
5. After entering the filter and slicer configuration and values, save and go to the Send Email tab. Set up and validate your sender mail ID and enter the email subject and body. Click on Update to complete your job setup and return to the Job Summary tab.
6. In the Job Summary tab, click on the Play button to run the Retail Sales Report and email sliced and filtered PDFs to your end-users. These users, both internal and external to your organization, do not require any Power BI license to view their reports.
Resources
