Design & Implementation of a Secure, High-Performance Power BI Sales Report

Hi Ericathomas ,

Thank you for reaching out to the Microsoft Community Forum.

 

Please try below steps.

1. Created sample data and built data model(star schema). Please refer below snaps.

 

 

 

2. Daily Refresh + Incremental Refresh (millions of rows)

In Power BI Desktop, Model view --> Table (FactSales) --> Incremental refresh and real-time data. Refresh rows in the last: 5 Years (your horizon). Incrementally refresh data starting: 3 Days (your incremental window). Publish to the Service, configure Scheduled Refresh (Daily).

 

Please refer below link for increment

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft Learn

 

Note: If data is on-prem: configure an On-premises Data Gateway. If data is in OneDrive/SharePoint: refresh can be cloud-native.

 

3. Security: RLS by Region (users see only their region):

 

Create role --> Region RLS, Add DAX filter on DimRegion:

 

DimRegion[RegionKey] IN VALUES(SecurityUsers[RegionKey])

 

 

Please refer below link.

Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn

 

After publishing, assign users/groups to the Region RLS role in the workspace/app.

 

4. Performance for Millions of Rows:

a. Build model in Star schema (FactSales + small dimensions)
b. Detail table: Use DirectQuery to your SQL Warehouse/Lakehouse or Direct Lake if Fabric.
c. Composite model with Aggregations: Create FactSalesAgg with rollups by DateKey, RegionKey containing SumQuantity, SumSales. Map its aggregations to the columns/measures of the DirectQuery detail table. Most visuals hit the Import agg cache. Drill-through or very detailed queries spill to DirectQuery.

 

5. Storage Modes: DimDate/DimRegion/DimCustomer --> Import mode. In composite models, set DimDate and DimRegion to Dual to reuse cache on both sides.

 

6. Created DAX measures for reporting.

Total Sales = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice] * (1 - FactSales[DiscountRate]))
Total Quantity = SUM(FactSales[Quantity])
Average Selling Price = DIVIDE([Total Sales], [Total Quantity])
Distinct Customers = DISTINCTCOUNT(FactSales[CustomerKey])
YoY Sales = CALCULATE([Total Sales], DATEADD(DimDate[FullDate], -1, YEAR))
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[FullDate]))
Sales MTD = CALCULATE([Total Sales], DATESMTD(DimDate[FullDate]))
Sales QTD = CALCULATE([Total Sales], DATESQTD(DimDate[FullDate]))
Sales YTD = CALCULATE([Total Sales], DATESYTD(DimDate[FullDate]))

 

7. Please refer below output snap and attached pbix file.

 

 

I hope this information helps. Please do let us know if you have any further queries.

 

Regards,

Dinesh