Ensuring Correct Filter and Slicer Application in BI Helper

WARNING: This is a detailed technical article with best practice guidance for correct filter and slicer application in BI Helper. It also provides issue diagnosis and resolution steps for errors in filter and slicer applications. 

Applying filters and slicers to create and distribute end-user-specific PDFs is at the very heart of BI Helper. This article provides comprehensive guidance on resolving any issues of incorrectly applied filters and slicers. 

Users occasionally face an issue where the filters or slicers selected by them in Power BI  are not applied consistently across all tabs and visuals in the PDFs generated using BI Helper. This results in some PDFs having unfiltered (all slicer/filter values applied) or incorrectly filtered visuals. This is a serious data security and privacy issue and needs to be addressed by the user as soon as it is noticed. 

An offshoot of this problem is when blank pages or visuals are found in BI Helper-generated PDFs. 

This issue can occur due to an error in one of three places:

1. Interaction of Power BI with BI Helper
2. Power BI data model and report
3. Caching in Power BI Service (Azure)

The various causes of this issue and the steps to resolve them (in diagnostic sequence) are given below.  

‍

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. If you are using filters, note that BI Helper only supports report filters, and not page or visual filters. 
3. If you are using slicers, check that you are using slicer titles, NOT headers, as the input to BI Helper. 
4. Check that the filter and slicer input syntax is as per the BI Helper documentation, which is accessible from the Generate PDF tab in BI Helper.

‍

Power BI Data Model and Report

This section covers cases where a problem with the data model, cross-filtering, visual interaction, or some error in the input of slicer values to BI Helper causes filter and slicer mismatch in BI Helper PDFs. 

The diagnostics and solutions in this section are pointers to robust and trouble-free report design in Power BI. 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 into the data warehouse that feeds Power BI
   • Power BI data refresh from warehouse tables. This can cause blank pages or visuals in the BI Helper snapshot of the Power BI report. It can also cause the prior filter or slicer value to persist in a page snapshot, resulting in a mismatch. The problem is amplified if there are multiple data loads or Power BI refreshes in a day. 

        Solution: Simplify your Power BI report and reduce its size as much as possible, or create a separate ‘lite’ version of your report to run the BI Helper job.

2. Unique values of filter and slicer input: Using a filter or slicer with duplicate values in the source table can cause slicer mismatch errors 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 you have some visuals that summarize by customer, then make sure that you pick a unique value column from the Customer table as a filter/slicer input‍
4. Slicers must be synced: Ensure that your slicers are synced (NOT copied) across 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 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 filter. DO NOT do both at the same time.

‍

Caching in Power BI Service (Azure)

Caching in Power BI Service occurs randomly (and very rarely) in a BI Helper job run, and results in a slicer value not getting completely cleared from all the pages/visuals before the next slicer value is applied. 

There is no consistency across batches on which slicer values apply correctly and which ones are carried forward from the prior PDF. Or on which pages are affected, and which are not.

Fortunately, this 'corruption' of the Azure cache is very rare. We have only observed it 2-3 times in thousands of Power BI reports and BI Helper jobs.

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

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 as well. 
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 report 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 filter and slicer values. 
5. Verify that the filters and slicers are applied correctly to all pages and visuals in all the test PDFs. 

If your slicer mismatch problem persists after you apply all the above steps, please email support@bihelper.tech. 

‍‍

Further Analysis of Azure Caching

Slicer mismatch due to Azure caching is sometimes seen in BI Helper PDFs and not in the PDFs generated using the Export to File option in the 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 for PDF creation. 

Note: With the Export to File option, users have to manually generate one PDF at a time, change filter and slicer values each time, then manually email each PDF to the intended users. This is not a feasible method to create and distribute reports as the volumes increase. 

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 file size, and 50 exports per API call. Composite semantic models with at least one external data source with SSO are not supported either.

This is why Power BI Premium customers often prefer to use BI Helper for their report bursting requirements, even though it is available in their license itself.