Complaints MI Pack
A Power BI complaints MI pack for a UK regulated firm, built on a Fabric Lakehouse fed by Dynamics 365, SQL Server and Excel reference files. It serves the monthly Customer and Risk Committee pack and a daily team-leader view secured by row-level security. It tracks volumes, 8-week resolution, uphold rate, redress, Ombudsman referrals and complaints per 1,000 customers against target and prior year.
Audience: Customer Committee, Risk Committee and complaints team leaders. Generated by Blueprint BI on 2026-11-19 and shown exactly as generated.
What was entered
These are the only inputs. Everything below was generated from them.
- Industry
- Financial Services
- Report type
- Complaints MI
- BI tool
- Power BI
- Currency
- GBP (£)
- Data sources
- Dynamics 365, SQL Server, Excel / CSV
- Columns
- ComplaintID, ReceivedDate, LoggedDate, FinalResponseDate, ClosedDate, ProductLine, Channel, RootCause, Outcome, RedressAmount, FOSReferredDate, FOSOutcome, TeamID, HandlerID, VulnerableFlag
- KPIs
- Complaints Received (target 1,200), Resolved Within 8 Weeks % (target 95%), Uphold Rate % (target 35%), Complaints per 1,000 Customers (target 2.5), Ombudsman Referral Rate % (target 5%)
- Notes
- Monthly committee pack plus a daily operational view for team leaders. Team leaders should only see their own team's cases. Customer counts for complaints per 1,000 come from a monthly extract of active customers by product line.
- Style and pages
- Corporate Clean, 3 pages
Scope
Purpose, default period and where each input column lands in the model.
- Purpose
- How many complaints are we receiving, resolving and holding open, how quickly and fairly are we resolving them, and where are root causes, redress and Ombudsman referrals concentrated?
- Audience
- Customer Committee, Risk Committee and complaints team leaders
- Default period
- Committee pages: last complete calendar month with a 13-month trend. Team page: as at today. Refreshed daily.
| Source column | Model destination | Notes |
|---|---|---|
| ComplaintID | FactComplaint[ComplaintID] | Dynamics 365 case number kept as a degenerate key; distinct-counted by all volume measures. |
| ReceivedDate | FactComplaint[ReceivedDateKey], DimDate[Date] | Converted to a yyyymmdd integer key; active relationship to DimDate; start of the 8-week clock. |
| LoggedDate | FactComplaint[LoggedDate] | Kept as a date attribute for logging-delay data quality checks; not used for the 8-week clock. |
| FinalResponseDate | FactComplaint[FinalResponseDateKey], FactComplaint[DaysToFinalResponse] | yyyymmdd key with an inactive relationship to DimDate; 99991231 when no final response has been sent yet. |
| ClosedDate | FactComplaint[ClosedDateKey] | yyyymmdd key with an inactive relationship to DimDate; 99991231 while the case is still open in Dynamics. |
| ProductLine | DimProductLine[ProductLine], FactComplaint[ProductLineKey] | Conformed with the product lines in the customer extract so complaints per 1,000 joins correctly. |
| Channel | DimChannel[Channel], FactComplaint[ChannelKey] | Dynamics option-set labels mapped to the surrogate key. |
| RootCause | DimRootCause[RootCause], FactComplaint[RootCauseKey] | Grouped into RootCauseCategory using the Excel root-cause taxonomy. |
| Outcome | FactComplaint[Outcome], FactComplaint[IsUpheld] | Values: Fully upheld, Partially upheld, Not upheld, Withdrawn. IsUpheld is derived from them by the Upheld rule. |
| RedressAmount | FactComplaint[RedressAmount] | GBP, DECIMAL(18,2); null converted to 0. |
| FOSReferredDate | FactComplaint[FOSReferredDateKey] | yyyymmdd key with an inactive relationship to DimDate; 99991231 when the complaint has not been referred. |
| FOSOutcome | FactComplaint[FOSOutcome] | Values: Upheld, Not upheld, Settled, Pending; 'Not referred' when blank. |
| TeamID | DimTeam[TeamID], FactComplaint[TeamKey] | Team that owned the case when it was received; drives row-level security. |
| HandlerID | DimHandler[HandlerID], FactComplaint[HandlerKey] | Current case handler, mapped to the surrogate key. |
| VulnerableFlag | FactComplaint[VulnerableFlag] | Source bit converted to 'Yes'/'No' text for slicing. |
Business rules
- Upheld complaint. Outcome is 'Fully upheld' or 'Partially upheld'. Withdrawn cases are excluded from the uphold denominator. Calculated in: Gold layer, FactComplaint[IsUpheld], every load
- Resolved within 8 weeks. A final response is sent no more than 56 calendar days after ReceivedDate (DaysToFinalResponse <= 56). Calculated in: Gold layer computes DaysToFinalResponse; the measure applies the 56-day test
- Open complaint. Received on or before the as-at date and no final response sent by that date. Breaching once older than 56 days. Calculated in: Semantic model measures [Open Complaints] and [Open Past 8 Weeks]
- Ageing band. Age of an open case at refresh, banded as 0-3 days, 4-14 days, 15-28 days, 29-56 days, Over 8 weeks. Resolved cases are banded 'Resolved'. Calculated in: Gold layer, daily load
1. Architecture
From source systems to the semantic model and the people who use it.
Data flow detail
- Ingestion
- Changed cases land daily in Bronze; the customer extract is loaded monthly and the reference files reloaded in full.
- Bronze
- Raw, append-only copies of the source tables with load metadata.
- Silver
- Cleaned and deduplicated cases with conformed product lines and validated dates.
- Gold
- Star schema with surrogate keys, derived flags, ageing bands and date keys.
- Semantic model
- Imports the Gold star schema daily, with row-level security by team and all measures in _Measures.
- Caveat: Data is as at the last refresh, so ageing bands reflect the refresh date rather than intraday status.
- Caveat: Scheduled refreshes are limited to 8 per day on Pro and 48 on Premium or Fabric capacity.
- Caveat: Changes to RLS mappings take effect only after the next refresh.
Governance & security
- RLS role 'Team Leader' filters DimTeam[TeamLeaderUPN] = USERPRINCIPALNAME(); committee and MI users get an unrestricted role.
- Data quality checks: FinalResponseDate before ReceivedDate, unmapped root causes and missing product lines are quarantined and reported.
- Daily refresh monitoring with failure alerts; monthly committee data frozen at working day 3.
- Microsoft Purview lineage and sensitivity label 'Confidential - Customer'.
- Measure definitions are signed off by the Head of Complaints and documented in the model.
Source detail
- Complaints case management: System of record for complaint cases, dates, outcomes, redress and Ombudsman referrals.
- Customer data warehouse: Monthly extract of active customers by product line, taken at month end.
- Handler and team directory: Handler names, team membership and team leader user principal names (UPNs) for row-level security.
- Root cause taxonomy: Maps root causes to categories approved by Complaints Quality Assurance.
| Refresh and alerts | Ready by 07:00 daily; refresh failures alert the BI support Teams channel and the Complaints MI Manager. |
|---|---|
| Storage mode | Import |
| History | 5 years of complaints and customer counts, covering a 13-month trend plus prior year and FCA record-keeping needs. |
| Environments | Dev, Test and Prod Fabric workspaces |
| Deployment | Fabric deployment pipelines with deployment rules for connections |
| Source control | Semantic model and report stored as PBIP in Azure DevOps Git |
2. Data model
Star schema with grain, keys and relationships.
Star schema (galaxy with two facts)
2 fact · 6 dimension tables · 11 relationships · measures on _Measures
- DateKeyINT
- DateDATE
- YearINT
- QuarterNVARCHAR(7)sort by YearQuarterNumber
- YearQuarterNumberINT
- MonthNumberINT
- MonthNameNVARCHAR(10)sort by MonthNumber
- MonthYearNVARCHAR(8)sort by YearMonthNumber
- YearMonthNumberINT
- IsWorkingDayBIT
- ChannelKeyINT
- ChannelNVARCHAR(30)Business key
- TeamKeyINT
- TeamIDNVARCHAR(20)Business key
- TeamNameNVARCHAR(50)
- SiteNVARCHAR(50)
- TeamLeaderUPNNVARCHAR(100)
- ComplaintIDNVARCHAR(20)Degenerate dimension
- ReceivedDateKeyINT→ DimDate[DateKey]
- LoggedDateDATE
- FinalResponseDateKeyINT→ DimDate[DateKey]
- ClosedDateKeyINT→ DimDate[DateKey]
- ProductLineKeyINT→ DimProductLine[ProductLineKey]
- ChannelKeyINT→ DimChannel[ChannelKey]
- RootCauseKeyINT→ DimRootCause[RootCauseKey]
- TeamKeyINT→ DimTeam[TeamKey]
- HandlerKeyINT→ DimHandler[HandlerKey]
- OutcomeNVARCHAR(30)
- IsUpheldBIT
- RedressAmountDECIMAL(18,2)
- FOSReferredDateKeyINT→ DimDate[DateKey]
- FOSOutcomeNVARCHAR(30)
- VulnerableFlagNVARCHAR(3)
- DaysToFinalResponseINT
- AgeingBandNVARCHAR(20)sort by AgeingBandSort
- AgeingBandSortINT
- MonthEndDateKeyINT→ DimDate[DateKey]
- ProductLineKeyINT→ DimProductLine[ProductLineKey]
- ActiveCustomersINT
- ProductLineKeyINT
- ProductLineNVARCHAR(50)Business key
- BusinessAreaNVARCHAR(50)
- RootCauseKeyINT
- RootCauseNVARCHAR(100)Business key
- RootCauseCategoryNVARCHAR(50)
- HandlerKeyINT
- HandlerIDNVARCHAR(20)Business key
- HandlerNameNVARCHAR(100)
- IsActiveBIT
Grain note. FactActiveCustomers has no relationship to DimChannel, DimRootCause, DimTeam, DimHandler, so its measures are only meaningful by the other dimensions.
| From (many) | To (one) | Cardinality | Filter direction | Active |
|---|---|---|---|---|
| FactComplaint.ReceivedDateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactComplaint.FinalResponseDateKey | DimDate.DateKey | many-to-one | Single | Inactive |
| FactComplaint.ClosedDateKey | DimDate.DateKey | many-to-one | Single | Inactive |
| FactComplaint.FOSReferredDateKey | DimDate.DateKey | many-to-one | Single | Inactive |
| FactComplaint.ProductLineKey | DimProductLine.ProductLineKey | many-to-one | Single | Yes |
| FactComplaint.ChannelKey | DimChannel.ChannelKey | many-to-one | Single | Yes |
| FactComplaint.RootCauseKey | DimRootCause.RootCauseKey | many-to-one | Single | Yes |
| FactComplaint.TeamKey | DimTeam.TeamKey | many-to-one | Single | Yes |
| FactComplaint.HandlerKey | DimHandler.HandlerKey | many-to-one | Single | Yes |
| FactActiveCustomers.MonthEndDateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactActiveCustomers.ProductLineKey | DimProductLine.ProductLineKey | many-to-one | Single | Yes |
3. DAX measures
An excerpt: the first 6 of 33 measures. Download the .dax file for all of them.
Complaints Received =
DISTINCTCOUNT ( FactComplaint[ComplaintID] )Complaints Received PY =
CALCULATE ( [Complaints Received], SAMEPERIODLASTYEAR ( DimDate[Date] ) )Complaints Received Monthly Target =
1200Complaints Received Target =
VAR Months = COUNTROWS ( VALUES ( DimDate[YearMonthNumber] ) )
VAR IsSliced = ISFILTERED ( DimProductLine ) || ISFILTERED ( DimChannel ) || ISFILTERED ( DimRootCause ) || ISFILTERED ( DimTeam ) || ISFILTERED ( DimHandler )
RETURN IF ( IsSliced || Months = 0, BLANK (), [Complaints Received Monthly Target] * Months )Complaints Received vs Target % =
VAR Tgt = [Complaints Received Target]
RETURN IF ( ISBLANK ( Tgt ) || Tgt <= 0, BLANK (), DIVIDE ( [Complaints Received] - Tgt, Tgt ) )Complaints Resolved =
CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), USERELATIONSHIP ( FactComplaint[FinalResponseDateKey], DimDate[DateKey] ) )4. KPIs and RAG rules
| KPI | Target | Direction | Green (1) | Amber (2) | Red (3) | Example |
|---|---|---|---|---|---|---|
Complaints Received Distinct complaints received in the month, by received date. [Complaints Received] RAG: [Complaints Received RAG] | 1,200 per month | Lower | ≤ 1,200 | ≤ 1,320 | > 1,320 | 1,184 -3.1% Green |
Resolved Within 8 Weeks % Final responses sent within 56 days of receipt / all final responses. [Resolved Within 8 Weeks %] RAG: [Resolved Within 8 Weeks RAG] | 95% | Higher | ≥ 95.0% | ≥ 90.0% | < 90.0% | 92.4% -1.2 pts Amber |
Uphold Rate % Fully and partially upheld complaints / resolved complaints, excluding withdrawn. [Uphold Rate %] RAG: [Uphold Rate RAG] | 35% | Lower | ≤ 35.0% | ≤ 40.0% | > 40.0% | 41.2% +2.6 pts Red |
Complaints per 1,000 Customers Monthly complaints received / active customers at month end x 1,000. [Complaints per 1,000 Customers] RAG: [Complaints per 1,000 Customers RAG] | 2.5 | Lower | ≤ 2.50 | ≤ 3.00 | > 3.00 | 2.38 -0.07 Green |
Ombudsman Referral Rate % Complaints referred to the Ombudsman / final responses in the period. [Ombudsman Referral Rate %] RAG: [Ombudsman Referral Rate RAG] | 5% | Lower | ≤ 5.0% | ≤ 7.0% | > 7.0% | 5.8% +0.4 pts Amber |
How RAG is shown
Each KPI has a RAG measure returning 1, 2 or 3 (BLANK when there is no value), so the thresholds live in one place. In Power BI, use conditional formatting with Format style "Rules" based on the RAG measure: 1 green, 2 amber, 3 red, and an icon set on the same rule. Status is never shown by colour alone: every colour is paired with its icon shape or word.
5. Report pages
Page layouts with the theme applied. Charts show placeholder shapes; the KPI cards use Claude's sample values.
Page 1: Executive Summary
- Purpose
- Monthly committee view of complaint KPIs against target and prior year.
- Audience
- Customer Committee and Risk Committee
- Default filter
- Last complete calendar month, with a 13-month trend
- Slicers
- DimDate[MonthYear] (dropdown): Single select; defaults to the last complete month. · DimProductLine[BusinessArea] (buttons): Targets blank when filtered, by design.
- Title label
- [Selected Period Label]
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Complaints KPIs | kpi strip | values: [Complaints Received], [Resolved Within 8 Weeks %], [Uphold Rate %], [Complaints per 1,000 Customers], [Ombudsman Referral Rate %] |
| Are the five headline complaint KPIs on target this month? |
| Complaints Received vs Target and Prior Year | line | axis: DimDate[MonthYear] values: [Complaints Received], [Complaints Received PY], [Complaints Received Target] tooltip: [Complaints Received vs Target %] |
| Is complaint volume trending above target or last year? |
| Resolved Within 8 Weeks % by Month | column | axis: DimDate[MonthYear] values: [Resolved Within 8 Weeks %] tooltip: [Resolved Within 8 Weeks % PY], [Resolved Within 8 Weeks Variance] |
| Are we meeting the 8-week final response deadline each month? |
| Uphold Rate % by Product Line | bar | axis: DimProductLine[ProductLine] values: [Uphold Rate %] tooltip: [Uphold Rate % PY], [Complaints Resolved] |
| Which products uphold the most complaints? |
| Complaints per 1,000 Customers by Product Line | bar | axis: DimProductLine[ProductLine] values: [Complaints per 1,000 Customers] tooltip: [Active Customers], [Complaints per 1,000 Customers PY] |
| Which products generate the most complaints relative to customer base? |
Page 2: Root Cause, Redress and Ombudsman
- Purpose
- Explains what drives complaints, the redress paid and how the Ombudsman rules.
- Audience
- Customer Committee, Risk Committee and Complaints Quality Assurance
- Default filter
- Last 3 complete calendar months
- Slicers
- DimDate[MonthYear] (dropdown): Multi-select allowed. · DimProductLine[ProductLine] (dropdown) · DimChannel[Channel] (buttons)
- Title label
- [Selected Period Label]
| Name | Actual | vs Target |
|---|---|---|
| Current Accounts | £1334K | 97.4% |
| Credit Cards | £1204K | 87.9% |
| Mortgages | £1091K | 79.6% |
| Personal Loans | £1298K | 94.8% |
| Savings | £1334K | 97.4% |
| Home Insurance | £1121K | 81.8% |
- Upheld
- Not upheld
- Settled
- Pending
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Top 10 Root Causes | bar | axis: DimRootCause[RootCause] values: [Complaints Received] tooltip: [Complaints Received PY], DimRootCause[RootCauseCategory] |
| Which root causes drive the most complaints? |
| Redress Paid by Root Cause Category | bar | axis: DimRootCause[RootCauseCategory] values: [Redress Paid] tooltip: [Uphold Rate %] |
| Where is redress cost concentrated? |
| Complaints by Product and Channel | matrix | rows: DimProductLine[ProductLine] columns: DimChannel[Channel] values: [Complaints Received] |
| Which product and channel combinations carry most complaints? |
| Ombudsman Outcomes | donut | legend: FactComplaint[FOSOutcome] values: [FOS Referrals] |
| How does the Ombudsman rule on referred complaints? |
| Ombudsman Referral Rate % Trend | line | axis: DimDate[MonthYear] values: [Ombudsman Referral Rate %], [Ombudsman Referral Rate % PY] tooltip: [FOS Referrals] |
| Is the share of complaints escalating to the Ombudsman rising? |
Page 3: Team Operations
- Purpose
- Daily view of open cases, ageing and 8-week breaches for team leaders.
- Audience
- Complaints team leaders
- Default filter
- As at today (latest refresh)
- Slicers
- DimTeam[TeamName] (dropdown): RLS restricts team leaders to their own team; unrestricted for MI users. · DimProductLine[ProductLine] (dropdown) · FactComplaint[VulnerableFlag] (buttons): Filters to vulnerable customers.
- Drill-through from
- Executive Summary
- Title label
- [Selected Period Label]
| Name | Actual | vs Target |
|---|---|---|
| CMP-104522 | £1512K | 110.3% |
| CMP-104587 | £1272K | 92.8% |
| CMP-104611 | £1383K | 100.9% |
| CMP-104690 | £1525K | 111.3% |
| CMP-104702 | £1255K | 91.6% |
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Team Position Today | kpi strip | values: [Open Complaints], [Open Past 8 Weeks], [Complaints Resolved], [Resolved Within 8 Weeks %] |
| How many cases are open, breaching and resolved on time? |
| Open Complaints by Ageing Band | stacked column | axis: FactComplaint[AgeingBand] legend: FactComplaint[VulnerableFlag] values: [Open Complaints] |
| How old is the open caseload? |
| Open Past 8 Weeks by Handler | bar | axis: DimHandler[HandlerName] values: [Open Past 8 Weeks] tooltip: [Open Complaints] |
| Which handlers hold cases past the 8-week deadline? |
| Resolved Within 8 Weeks % by Handler | bar | axis: DimHandler[HandlerName] values: [Resolved Within 8 Weeks %] tooltip: [Complaints Resolved] |
| Which handlers miss the 8-week deadline most? |
| Open Case List | table | values: FactComplaint[ComplaintID], DimProductLine[ProductLine], DimRootCause[RootCause], DimHandler[HandlerName], FactComplaint[AgeingBand], FactComplaint[VulnerableFlag], [Open Complaints] |
| Which individual cases need action today? |
6. AI-designed page
Page 1 as drawn by the AI Design Studio (a Pro feature), unedited.
Assumptions and open questions
| # | Assumption or question | Impact if different |
|---|---|---|
| 1 | Upheld means fully or partially upheld, with withdrawn cases excluded from the denominator. | The IsUpheld rule and [Uphold Rate %] change, and the 35% target may need recalibrating. |
| 2 | The 8-week clock runs 56 calendar days from ReceivedDate, not LoggedDate, with no pause for customer delays. | DaysToFinalResponse must change to working days or LoggedDate, altering the 8-week and breach measures. |
| 3 | Committee reporting and prior-year comparisons use calendar months, not a fiscal calendar. | DimDate needs fiscal columns and the PY measures must switch from SAMEPERIODLASTYEAR to fiscal-period logic. |
| 4 | The 1,200 complaints target is firm-wide and monthly, with no product, channel or team targets. | A FactComplaintTarget table by product and month is needed, and the target measures stop blanking when sliced. |
| 5 | Active customer counts are at month end by product line only, so complaints per 1,000 is not sliced by channel, root cause, team or handler. | An account-level or channel-level extract would change FactActiveCustomers' grain and the blanking logic in the measure. |
| 6 | Team leader to team mapping comes from the SQL Server directory, and cases stay with the team that owned them at receipt. | If cases follow the current team, TeamKey is refreshed each load, or DimTeam becomes SCD2 for team history. |
| 7 | RedressAmount is total financial redress including goodwill payments, net of any refunds already applied to the account. | Redress would need to be split into separate columns and [Redress Paid] filtered to the agreed components. |
Recommendations
- Partition FactComplaint by received month with incremental refresh, refreshing the last 3 months daily so late final responses are captured.
- Freeze a month-end snapshot of the Gold tables on working day 3 so committee packs are reproducible after cases are reopened.
- Document every measure's description in the model and publish a glossary page aligned to DISP and Consumer Duty terminology.
- Use a dedicated RLS test account per team in the Test workspace and run 'View as role' checks before each deployment.
- Run Best Practice Analyzer in Tabular Editor in the CI pipeline to enforce naming, hidden keys and format strings.
- Train team leaders in a 30-minute session and track adoption through workspace usage metrics.