Day 22: Power BI Scenarios & Case Studies

Systematic diagnosis: Step 1 — Check the data. Go to Data View, look at the Revenue column for those months — are the values actually present in the imported data? If not, the issue is in the source or Power Query. Step 2 — Check the Date table. Do all months have corresponding dates in the Date table? If the Date table stops in June 2026 and data goes to August, July-August will be blank. Step 3 — Check the relationship. Is the fact table's date column correctly related to the Date table? Wrong data type on either side breaks the relationship silently. Step 4 — Check the measure. Does the measure use CALCULATE with a Date filter that might exclude those months?

This is the most common real-world Power BI problem. Approach: Step 1 — Establish which is correct (or if neither is). Step 2 — Align the scope: same time period? Same filters? Same metric definition? "Revenue" in Excel might exclude returns while Power BI includes them. Step 3 — Trace the data lineage in Power BI: what is the source → Power Query transformations → data model → DAX measure → visual? At which point does the number diverge from Excel? Step 4 — Check for data quality issues: duplicates in the join key causing double-counting, nulls being included/excluded differently, different date range due to how filters are applied. Step 5 — Document the definition agreed upon and update either source to match.

This requires Row-Level Security (RLS). Implementation: Create a Manager-SalesRep mapping table with columns: ManagerEmail and SalesRepID. In Power BI Desktop: Modelling → Manage Roles → New Role "Manager View" → DAX filter on the SalesRep table: [ManagerEmail] = USERPRINCIPALNAME(). This filters to only the reps where the manager's email matches. Publish to Service. In Service → dataset → Security → add each manager's email to the "Manager View" role. When they open the report, they see only their team's data. Test with "View as Role" in Desktop first.

Two approaches: Approach 1 — KPI visual: Insert KPI visual → Value = Actual measure → Target = Target measure. The visual automatically colours based on comparison. Limited formatting control. Approach 2 — Custom conditional formatting: Create a status measure: KPI Status = IF([Achievement %] >= 1, "🟢", IF([Achievement %] >= 0.9, "🟡", "🔴")). Display in a table/matrix with conditional background formatting using a colour measure: KPI Colour = IF([Achievement %] >= 1, "#16a34a", IF([Achievement %] >= 0.9, "#d97706", "#dc2626")). Full control over the appearance.

Size reduction strategy: Step 1 — Identify large tables: View → Model View, check table sizes. Step 2 — Remove unused columns: in Power Query, delete every column not used in visuals, measures, or relationships. Step 3 — Aggregate detail data: if visuals only need monthly summaries, Group By in Power Query to summarise before loading. Step 4 — Switch to DirectQuery for large tables: keep the large sales table as DirectQuery, import smaller dimension tables. Step 5 — Build aggregation tables: create a monthly summary table that Power BI uses for high-level queries and falls back to detail only for drill-down. Step 6 — Consider Premium: for 10M rows with full Import mode, Premium capacity gives more headroom.