PERFORMANCE ISSUES IN PAGINTED REPORT BUILDER
We have a materialized view in Databricks with about 1.5 million rows and 80+ columns. On top of that, I’ve created a semantic model in Power BI, and a paginated report that shows essentially all 80+ columns (so it’s returning the full 1.5 million rows). The report also has around 24 filters/parameters. Because of this combination (large dataset + many columns + many filters), the paginated report is taking a long time to load. The main dataset is generated using the Query Designer, which produces a large: `EVALUATE SUMMARIZECOLUMNS(...)` So far, to improve performance I have: 1. **Created lookup tables for parameter datasets** Instead of retrieving dropdown values from the large flattened dataset, I created lookup tables and updated parameter datasets to use these tables. In the Databricks view we have 3 date columns: **D1, D2, D3**. In the Power BI semantic model I’ve joined these to `dim_date`. In the paginated report I want to expose 6 date parameters: **StartD1, EndD1, StartD2, EndD2, StartD3, EndD3**. I have three main questions: **Q1. How should I handle these 6 date parameters in the main dataset?** Each date column has its own start/end range; I’d like an approach that is reasonable to maintain. **Q2. Is the current approach (one big flattened table from the view) the right one?** We actually started with a star schema in the Power BI semantic model and then moved to a single flattened view, as suggested by a senior data engineer. The issue was that many of the 80+ columns required calculated columns and a lot of transformations, so we pushed all of that logic into the Databricks view instead. **Q3. How else can I improve performance?** Right now the paginated report is effectively selecting all rows and almost all columns, with 24 filters on top. I’m looking for design patterns or best practices (e.g. alternative modelling, pre‑aggregation, splitting datasets, etc.) that would make this scenario perform better. Any guidance or examples would be really appreciated.
Power BI Report Builder Dynamic Page number inside Power BI PBIX Report
Hi all , I need to generate a report with 2 tables that have a dynamic number of rows. 90% of my report can be created using Power BI Desktop, however there are 2 tables with a very different number of rows...varying from 250, 500, 900 rows... so at least for these 2 tables I would need Power BI Report Builder (paginated reports) my question is if inserting into a PBIX Power BI with the component of Power BI desktop Paginated Report creates multiple pages when exporting to PDF I am looking for the best option and if MIXING powerbi report builder inside powerbi desktop is correct for my usecase. Because charts and interactivity are cool from powerbi side..but I need to export tables in PDF with 950 rows.... Regards4 Filters for Values using DAX in Report Builder
I am working on a Paginated report in REPORT BUILDER that the user would like to: Select date (Month/Year) then the PhysicianOrganization then the PCPLocation to have a pdf for that location only. (PhysicianOrganization must filter PCPLocation) Below the Physician Organization does not filter on the PCPLocation and it must! Dataset1 will be filtered by PhysicianOrganization, PCPLocation and Date (monthly reporting) which used month and year and PhysicianLocation Dataset which has the Physician Organization & PCPLocation values which the Parameters connect to. Below is what the report data looks like and I added the DAX fields below (this is from a CUBE or Tabular connection) I am thinking I should put the Year and Month in the PhysicianLocation dataset? I have two tables--- this contains 2 the PhysicianOrganization and PCPLocation parameter that are referenced in the Parameter short dataset: EVALUATE SUMMARIZECOLUMNS( 'Providers'[PhysicianOrganization] ,'Providers'[PCPLocation] ) The main dataset that has all the values but PhysicianOrganization and PCPLocation have the parameter referenced but that points to the short 1) table above. Only the Hand filled in Parameters for Month and Year (all hand filled in) are in the main dataset. How can I get what is needed? I just need the report to be able to 1st filter by PhysicianOrganization then PCPLocation (subset of the PhysicianOrganization column). Here is Dataset1 where all 4 parameters are referenced : EVALUATE SUMMARIZECOLUMNS( 'Providers'[PCPLocation], 'Providers'[PhysicianOrganization], 'Patients'[InsuranceType], 'DateDim'[Year], 'DateDim'[MonthNameAbbreviation], FILTER( 'DateDim', 'DateDim'[Year] = VALUE(@Year) ), FILTER( 'Providers', 'Providers'[PhysicianOrganization] = @PhysicianOrganization && 'Providers'[PCPLocation] = @PCPLocation ), FILTER( 'DateDim', 'DateDim'[MonthNameAbbreviation] = @Month ), FILTER( 'Patients', 'Patients'[Populations] = "HAP HFHS Employees" ), "UniquePatients", [UniquePatients], "TotalCostYTD", [TotalCostYTD], "MemberMonthsYTD", [MemberMonthsYTD], "Admits1000YTD", [Admits1000YTD], "ERVisitsYTD", [ERVisitsYTD], "ERVisits1000YTD", [ERVisits1000YTD], "BloodPressureNumerator", [BloodPressureNumerator], "BloodPressureDenominator", [BloodPressureDenominator], "BloodPressureRate", [BloodPressureRate], "DiabetesHbA1cLessThan8Numerator", [DiabetesHbA1cLessThan8Numerator], "DiabetesDenominator", [DiabetesDenominator], "DiabetesHbA1cLessThan8Rate", [DiabetesHbA1cLessThan8Rate], "ReadmitsYTD", [ReadmitsYTD], "ReadmitsYTDRate", [ReadmitsYTD%], "AdmitsYTD", [AdmitsYTD], "AllowedCost", [AllowedCost], "AllowedCostYTD", [AllowedCostYTD], "AllowedPMPMYTD", [AllowedPMPMYTD], "BreastCancerScreeningNumerator", [BreastCancerScreeningNumerator], "BreastCancerScreeningDenominator", [BreastCancerScreeningDenominator], "BreastCancerScreeningRate", [BreastCancerScreeningRate], "ColorectalCancerScreeningDenominator", [ColorectalCancerScreeningDenominator], "ColorectalCancerScreeningNumerator", [ColorectalCancerScreeningNumerator], "ColorectalCancerScreeningRate", [ColorectalCancerScreeningRate], "ERVisitsRate", [ERVisitsYTDMoreThan5Visits1000], "ERVisitsNumerator", [ERVisitsYTDMoreThan5Visits], "VirtualNumerator", [OutpatientUtilYTD], "VirtualRate", [OutpatientUtil1000YTD], "DepressionScreeningNumerator", [DepressionScreeningNumerator], "DepressionScreeningDenominator", [DepressionScreeningDenominator], "DepressionScreeningRate", [DepressionScreeningRate], "DepressionPositiveScreenNumerator", [DepressionPositiveScreenNumerator], "DepressionPositiveScreenDenominator", [DepressionPositiveScreenDenominator], "DepressionPositiveScreenRate", [DepressionPositiveScreenRate], "CervicalCancerNumerator", [CervicalCancerNumerator], "CervicalCancerDenominator", [CervicalCancerDenominator], "CervicalCancerRate", [CervicalCancerRate], "GenericNumeratorYTD", [GenericNumeratorYTD], "GenericDenominatorYTD", [GenericDenominatorYTD], "GenericFillRateYTD", [GenericFillRateYTD], "September 2023 YTD", "September 2023 YTD", "Zero", 0 )
