Filters and slicers not being correctly applied to some tabs or visuals in BI Helper

Dec 22, 2024

WARNING: This is a detailed article with technical diagnosis and resolution methods 

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 BI Helper PDFs. 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 are generated in BI Helper PDFs, when some or all visuals in a page don’t load in time for the snapshot. 

This issue can occur due to an error at one of two places – inside Power BI or in the interaction of Power BI with BI Helper. The various causes and steps to resolve are detailed below (in diagnostic sequence).  

A. Power BI interaction 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 in the first page of your Power BI report. If you are using filters, please note that BI Helper only supports report level filters. 
  2. Check that you are using slicer titles, NOT headers, as the input to BI Helper. 
  3. Check that the filter and slicer input syntax is as per BI Helper documentation.
     

B. Power BI data model and report: Check and correct

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

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 supports PBIX files up to 1 GB in size, but in our experience building Power BI reports over the last seven years, we have consistently seen data issues when the PBIX crosses 350-400 MB. This is at two levels:some text
    • Data load from source apps 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 could 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.

  1. 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. 

  1. 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. 
  2. Ensure that your slicers are synced (NOT copied) across pages in your Power BI report. 
  3. 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. 

C. Caching in Azure (Power BI Service) where the report is published

This results in a slicer value not getting completely cleared from all the pages / visuals. 

It occurs randomly in a batch of reports and there is no consistency across batches on which slicer values apply correctly and which ones fail. Or which pages are affected, and which are not. Fortunately, this 'corruption' of the Azure cache is very rare. We have only seen it 2-3 times in all the years with 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 simply 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. In 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 to all pages. 
  3. Save the PBIX and republish it to a new Workspace in Power BI Service. The 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 the problem persists, please email support@bihelper.tech. 

Further analysis of Azure caching:

This slicer mismatch is sometimes seen by users only in BI Helper PDFs and not in the PDFs generated from Power BI service using the Export to File option. This is because Export to File Power BI REST API is not supported for PPU and Pro users. Even with Premium capacity, the export has the limitations detailed in the following link, including semantic models only on Premium or Embedded capacity, 250 MB file size, 50 exports per API call, composite semantic models with at least one external data source with SSO are not supported.

Export Power BI embedded analytics reports API - Power BI | Microsoft Learn