Applying Filters and Slicers in BI Helper - Technical Checklist

WARNING: Detailed technical article

Why is it important to apply filters and slicers correctly in BI Helper?

Applying filters and slicers to create and distribute user-specific PDF/ PPTX reports is the core function of BI Helper.

Users occasionally face an issue where filters or slicers selected by them in Power BI are not applied consistently in the BI Helper output, resulting in some visuals being unfiltered (all slicer/ filter values applied) or incorrectly filtered. This is a serious data security and privacy issue that needs to be addressed immediately. 

An offshoot of this problem is a PDF/ PPTX with blank pages or visuals. 

‍

How do I diagnose a filter/ slicer issue in BI Helper?

Filter/ slicer issues can occur in one of three areas:

1. Interaction of Power BI with BI Helper
2. Power BI Data Model and Report
3. Caching in Power BI Service (Microsoft Azure) - Slicer Mismatch

‍

Filters and Slicers in BI Helper: Input, Issue Diagnosis and Resolution

‍

This article provides a checklist for the input of filters and slicers in BI Helper. The checklist also helps diagnose and resolve any filter/ slicer issues in a BI Helper job. 

‍

Interaction of Power BI with BI Helper

This checklist addresses filter and slicer mismatch caused due to the interaction of Power BI and BI Helper, i.e., the proper application of filter and slicer syntax in BI Helper.  

1. Ensure that all filters and slicers used as inputs to BI Helper are on the first page of your Power BI report. 
2. Filters: BI Helper only supports report level filters in Power BI (Filters pane > Filters on all pages) and does not support filters applied at the page level or on individual visuals. 
3. Slicers: Ensure that you use slicer titles (NOT slicer headers) as the input to BI Helper. 
4. Check that the filter and slicer syntax is in line with the info button and documentation in the BI Helper Generate PDF tab.

‍

Power BI Data Model and Report

This section covers problems with the data model, cross-filtering, visual interaction or input error causing filter and slicer mismatch in the BI Helper output. 

The diagnostics and solutions in this section help make your Power BI reports robust and error-free. They are upstream of BI Helper and need to be addressed in Power BI. 

1. PBIX size: Large PBIX files and their published versions (in Power BI service) suffer from slow and unpredictable data refreshes and page loads. Microsoft states that Power BI Pro licensing supports PBIX files up to 1 GB in size, but in our experience building Power BI reports over the years, we have consistently seen data issues when the PBIX crosses 350-400 MB. This is at two levels:
   
   • Data load from source applications to the data warehouse that feeds Power BI
   • Power BI data refresh from warehouse tables

The result is blank pages or visuals in the BI Helper snapshot of the Power BI report. Or the prior filter or slicer value may persist in a snapshot, resulting in a mismatch. The problem is amplified as the frequency of data loads or Power BI refreshes increases. 

        Solution: Simplify your Power BI report (PBIX file) and reduce its size to 300 MB or less. Or create a separate ‘lite’ version for BI Helper.

2. Unique values of filter and slicer input: Using a filter or slicer with duplicate values in the source table can cause mismatches due to ambiguity in filtered values.

        Solution: Use columns with unique values as filter and slicer inputs to BI Helper. 

3. Table mapping hierarchy and direction: Ensure that your filters and slicers are picked from sufficiently upstream tables to correctly filter all relevant visuals in your Power BI report, and make sure that the joins with downstream tables are mapped in the correct direction. E.g., if your data model has Customer -> Location tables, and some visuals are summarized by customer, then ensure that you use a unique value column from the Customer table as the filter/ slicer input to BI Helper.
4. Slicers must be synced: Ensure that your slicers are synced (NOT copied) across all pages in your Power BI report. ‍
5. Remove interfering visual filters/ slicers from your Power BI report: Apply a given filter or slicer at only one place in your report. For a given filter/ slicer, do not repeat the slicer or apply report level filters in the visual filters pane. E.g., if Location is the input to BI Helper, then create a slicer titled Location from the appropriate table and column OR add it as a report level filter. DO NOT do both at the same time.

‍

Caching in Power BI Service (Microsoft Azure) - Slicer Mismatch

Caching in Power BI Service occurs randomly (and very rarely) and results in a slicer value not getting cleared from all the pages/ visuals before the next value is applied. There is no consistency across batches on which slicer values, pages or visuals are applied correctly and which ones carry forward from the prior PDF/ PPTX.

Fortunately, this 'corruption' of the Microsoft Azure cache is very rare. We have only observed it 2-3 times in tens of thousands of BI Helper job runs. Further, this error is only observed with slicers, not with filters.

The bad news is that there is no way to detect this issue. BI Helper reads and applies slicer values ONLY on the first page of the Power BI report and takes snapshots of all subsequent pages assuming that the slicer value has carried through. 

Solution: 

1. Open the PBIX file of your Power BI report. On the first page, delete the slicer that is not working correctly. Delete it from all the other pages too. 
2. Add back the slicer from the same table and column of your data model as before and sync it (do not copy it) to all the pages you want to publish.
3. Save the PBIX and republish it to a new Workspace in Power BI Service. This change of URL solves for the Azure caching. Clearing your local browser history does not solve the problem.
4. Set up the new Power BI report as a BI Helper job and run it with a set of test slicer values. 
5. Verify that the slicers are applied correctly to all pages and visuals in all the output PDF/ PPTX reports.

If your slicer mismatch problem persists after you apply all the above steps, consider replacing your slicers with report level filters (Filters pane > Filters on all pages).

‍‍

Further Analysis of Microsoft Azure Caching

Slicer mismatch due to Azure caching is sometimes seen in BI Helper PDF/ PPTX files and not in the files using the Export to File option in Power BI Service. This is because the Export to File Power BI REST API is not supported for Power BI Pro and PPU users, so BI Helper uses a different Power BI API to create PDF/ PPTX files. 

Note: With the Export to File option, users have to manually generate one output report at a time, change filter and slicer values each time, and then manually email each one to the right users. As the report volume increases, this is not a viable process.

Even with Power BI Premium capacity, automated PDF export has the limitations detailed in the following link Export Power BI embedded analytics reports API - Power BI | Microsoft Learn.

Limitations include semantic models being supported only on Power BI Premium or Embedded capacity, 250 MB output file size, and 50 exports per API call. Composite semantic models with at least one external data source with SSO are not supported.

That is why Power BI Premium customers often use BI Helper for their report bursting requirements, instead of their liccensed Power BI Premium/ Fabric capacity.