> Sample blueprint generated by Blueprint BI (https://app.milestonebi.com/samples/financial-services). Shown exactly as generated.

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

## 1. Scope and inputs

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

### Columns provided

| 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)

## 2. Data architecture

### Data sources
| Source | System | Refresh | Notes |
|---|---|---|---|
| Complaints case management | Dynamics 365 Customer Service (Dataverse) | Daily 01:00 | System of record for complaint cases, dates, outcomes, redress and Ombudsman referrals. |
| Customer data warehouse | SQL Server | Monthly, working day 2 | Monthly extract of active customers by product line, taken at month end. |
| Handler and team directory | SQL Server | Daily 01:00 | Handler names, team membership and team leader user principal names (UPNs) for row-level security. |
| Root cause taxonomy | Excel / CSV on SharePoint | On change, picked up daily | Maps root causes to categories approved by Complaints Quality Assurance. |

### Ingestion: Microsoft Fabric Data Factory pipelines with Dataverse Link to Fabric
Incremental load on Dataverse modifiedon watermark; full reload of reference files. Changed cases land daily in Bronze; the customer extract is loaded monthly and the reference files reloaded in full.

### Layers
- **Bronze** (Fabric Lakehouse (Delta)): Raw, append-only copies of the source tables with load metadata.
- **Silver** (Fabric Lakehouse notebooks (PySpark)): Cleaned and deduplicated cases with conformed product lines and validated dates.
- **Gold** (Fabric Warehouse): Star schema with surrogate keys, derived flags, ageing bands and date keys.

### Semantic layer: Power BI semantic model (Import)
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.

| Area | Design |
|---|---|
| Refresh and alerts | Ready by 07:00 daily; refresh failures alert the BI support Teams channel and the Complaints MI Manager. |
| 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 |

### Governance and 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.

## 3. Data model: Star schema (galaxy with two facts)

Measures are stored on a dedicated `_Measures` table.

### FactComplaint (fact)
_Grain: One row per complaint case_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| ComplaintID | NVARCHAR(20) | degenerate |  | Complaint case number. |
| ReceivedDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Date the complaint was received. |
| LoggedDate | DATE | date |  | Date the case was logged in Dynamics. |
| FinalResponseDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Final response date; 99991231 if none sent yet. |
| ClosedDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Case closure date; 99991231 if the case is open. |
| ProductLineKey | INT | foreign key | → DimProductLine[ProductLineKey]; hidden | Product line complained about. |
| ChannelKey | INT | foreign key | → DimChannel[ChannelKey]; hidden | Channel the complaint arrived through. |
| RootCauseKey | INT | foreign key | → DimRootCause[RootCauseKey]; hidden | Root cause of the complaint. |
| TeamKey | INT | foreign key | → DimTeam[TeamKey]; hidden | Owning team when the complaint was received. |
| HandlerKey | INT | foreign key | → DimHandler[HandlerKey]; hidden | Current case handler. |
| Outcome | NVARCHAR(30) | attribute |  | Firm's complaint outcome. |
| IsUpheld | BIT | attribute | hidden | True if the complaint was fully or partially upheld. |
| RedressAmount | DECIMAL(18,2) | additive | hidden | Redress paid in GBP. |
| FOSReferredDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Date of Ombudsman referral; 99991231 if not referred. |
| FOSOutcome | NVARCHAR(30) | attribute |  | Financial Ombudsman Service outcome. |
| VulnerableFlag | NVARCHAR(3) | attribute |  | Yes if the customer has a vulnerability flag. |
| DaysToFinalResponse | INT | non additive | hidden | Calendar days from receipt to final response. |
| AgeingBand | NVARCHAR(20) | attribute | sort by AgeingBandSort | Age band of the case at refresh. |
| AgeingBandSort | INT | attribute | hidden | Sort order for AgeingBand. |

### FactActiveCustomers (fact)
_Grain: One row per product line per month end_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| MonthEndDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Month-end date of the extract. |
| ProductLineKey | INT | foreign key | → DimProductLine[ProductLineKey]; hidden | Product line of the customer count. |
| ActiveCustomers | INT | semi additive | hidden | Active customers at month end. |

### DimDate (dimension · marked as date table)
_Grain: One row per calendar day, plus a 99991231 placeholder row_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| DateKey | INT | primary key | hidden | Date as a yyyymmdd integer. |
| Date | DATE | date |  | Calendar date. |
| Year | INT | attribute |  | Calendar year. |
| Quarter | NVARCHAR(7) | attribute | sort by YearQuarterNumber | Quarter label, e.g. 2025 Q1. |
| YearQuarterNumber | INT | attribute | hidden | Quarter sort key, e.g. 20251. |
| MonthNumber | INT | attribute | hidden | Month number from 1 to 12. |
| MonthName | NVARCHAR(10) | attribute | sort by MonthNumber | Month name. |
| MonthYear | NVARCHAR(8) | attribute | sort by YearMonthNumber | Month label, e.g. Jan 2025. |
| YearMonthNumber | INT | attribute | hidden | Month sort key, e.g. 202501. |
| IsWorkingDay | BIT | attribute |  | True for UK working days. |

### DimProductLine (dimension · SCD type 1)
_Grain: One row per product line_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| ProductLineKey | INT | surrogate key | hidden | Surrogate key. |
| ProductLine | NVARCHAR(50) | business key |  | Product line name. |
| BusinessArea | NVARCHAR(50) | attribute |  | Business area, e.g. Retail Banking or Lending. |

### DimChannel (dimension · SCD type 1)
_Grain: One row per complaint channel_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| ChannelKey | INT | surrogate key | hidden | Surrogate key. |
| Channel | NVARCHAR(30) | business key |  | Channel name, e.g. Telephone. |

### DimRootCause (dimension · SCD type 1)
_Grain: One row per root cause_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| RootCauseKey | INT | surrogate key | hidden | Surrogate key. |
| RootCause | NVARCHAR(100) | business key |  | Detailed root cause. |
| RootCauseCategory | NVARCHAR(50) | attribute |  | Root cause category from the taxonomy. |

### DimTeam (dimension · SCD type 1)
_Grain: One row per complaints team_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| TeamKey | INT | surrogate key | hidden | Surrogate key. |
| TeamID | NVARCHAR(20) | business key | hidden | Source team ID. |
| TeamName | NVARCHAR(50) | attribute |  | Team name. |
| Site | NVARCHAR(50) | attribute |  | Operating site. |
| TeamLeaderUPN | NVARCHAR(100) | attribute | hidden | Team leader login used by RLS. |

### DimHandler (dimension · SCD type 1)
_Grain: One row per case handler_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| HandlerKey | INT | surrogate key | hidden | Surrogate key. |
| HandlerID | NVARCHAR(20) | business key | hidden | Source handler ID. |
| HandlerName | NVARCHAR(100) | attribute |  | Handler display name. |
| IsActive | BIT | attribute |  | True if the handler is currently employed. |

### Relationships

| 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 | No |
| FactComplaint.ClosedDateKey | DimDate.DateKey | many-to-one | single | No |
| FactComplaint.FOSReferredDateKey | DimDate.DateKey | many-to-one | single | No |
| 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 |

## 4. DAX measures

33 measures in 9 display folders.

### Volumes

**Complaints Received** · `#,0`
```dax
Complaints Received =
DISTINCTCOUNT ( FactComplaint[ComplaintID] )
```
Complaints received in the period, by received date.

**Complaints Resolved** · `#,0`
```dax
Complaints Resolved =
CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), USERELATIONSHIP ( FactComplaint[FinalResponseDateKey], DimDate[DateKey] ) )
```
Complaints given a final response in the period.

**Open Complaints** · `#,0`
```dax
Open Complaints =
VAR AsAtDate = MIN ( MAX ( DimDate[Date] ), TODAY () )
VAR AsAtKey = YEAR ( AsAtDate ) * 10000 + MONTH ( AsAtDate ) * 100 + DAY ( AsAtDate )
RETURN CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), REMOVEFILTERS ( DimDate ), FactComplaint[ReceivedDateKey] <= AsAtKey, FactComplaint[FinalResponseDateKey] > AsAtKey )
```
Complaints without a final response at the last date in context.

### Time Intelligence

**Complaints Received PY** · `#,0`
```dax
Complaints Received PY =
CALCULATE ( [Complaints Received], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Complaints received in the same period last year.

**Resolved Within 8 Weeks % PY** · `0.0%`
```dax
Resolved Within 8 Weeks % PY =
CALCULATE ( [Resolved Within 8 Weeks %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
8-week resolution rate in the same period last year.

**Uphold Rate % PY** · `0.0%`
```dax
Uphold Rate % PY =
CALCULATE ( [Uphold Rate %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Uphold rate in the same period last year.

**Complaints per 1,000 Customers PY** · `0.00`
```dax
Complaints per 1,000 Customers PY =
CALCULATE ( [Complaints per 1,000 Customers], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Complaints per 1,000 customers in the same period last year.

**Ombudsman Referral Rate % PY** · `0.0%`
```dax
Ombudsman Referral Rate % PY =
CALCULATE ( [Ombudsman Referral Rate %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Ombudsman referral rate in the same period last year.

### Targets

**Complaints Received Monthly Target** · `#,0`
```dax
Complaints Received Monthly Target =
1200
```
Firm-wide monthly complaints received target.

**Complaints Received Target** · `#,0`
```dax
Complaints 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 )
```
Monthly target times months in context; blank when sliced below firm level.

**Complaints Received vs Target %** · `+0.0%;-0.0%;0.0%`
```dax
Complaints Received vs Target % =
VAR Tgt = [Complaints Received Target]
RETURN IF ( ISBLANK ( Tgt ) || Tgt <= 0, BLANK (), DIVIDE ( [Complaints Received] - Tgt, Tgt ) )
```
Percentage by which received complaints differ from target.

**Resolved Within 8 Weeks Target** · `0.0%`
```dax
Resolved Within 8 Weeks Target =
0.95
```
Target share resolved within 8 weeks.

**Resolved Within 8 Weeks Variance** · `+0.0%;-0.0%;0.0%`
```dax
Resolved Within 8 Weeks Variance =
VAR Actual = [Resolved Within 8 Weeks %]
RETURN IF ( ISBLANK ( Actual ), BLANK (), Actual - [Resolved Within 8 Weeks Target] )
```
Percentage-point gap to the 8-week target.

**Uphold Rate Target** · `0.0%`
```dax
Uphold Rate Target =
0.35
```
Target maximum uphold rate.

**Uphold Rate Variance** · `+0.0%;-0.0%;0.0%`
```dax
Uphold Rate Variance =
VAR Actual = [Uphold Rate %]
RETURN IF ( ISBLANK ( Actual ), BLANK (), Actual - [Uphold Rate Target] )
```
Percentage-point gap to the uphold rate target.

**Complaints per 1,000 Target** · `0.00`
```dax
Complaints per 1,000 Target =
2.5
```
Target maximum monthly complaints per 1,000 customers.

**Complaints per 1,000 Variance** · `+0.00;-0.00;0.00`
```dax
Complaints per 1,000 Variance =
VAR Actual = [Complaints per 1,000 Customers]
RETURN IF ( ISBLANK ( Actual ), BLANK (), Actual - [Complaints per 1,000 Target] )
```
Gap to the complaints per 1,000 target.

**Ombudsman Referral Rate Target** · `0.0%`
```dax
Ombudsman Referral Rate Target =
0.05
```
Target maximum Ombudsman referral rate.

**Ombudsman Referral Rate Variance** · `+0.0%;-0.0%;0.0%`
```dax
Ombudsman Referral Rate Variance =
VAR Actual = [Ombudsman Referral Rate %]
RETURN IF ( ISBLANK ( Actual ), BLANK (), Actual - [Ombudsman Referral Rate Target] )
```
Percentage-point gap to the referral rate target.

### Timeliness

**Resolved Within 8 Weeks %** · `0.0%`
```dax
Resolved Within 8 Weeks % =
VAR Resolved = [Complaints Resolved]
VAR Within8 = CALCULATE ( [Complaints Resolved], FactComplaint[DaysToFinalResponse] <= 56 )
RETURN IF ( Resolved > 0, DIVIDE ( Within8, Resolved ) )
```
Share of final responses sent within 56 days of receipt.

**Open Past 8 Weeks** · `#,0`
```dax
Open Past 8 Weeks =
VAR AsAtDate = MIN ( MAX ( DimDate[Date] ), TODAY () )
VAR AsAtKey = YEAR ( AsAtDate ) * 10000 + MONTH ( AsAtDate ) * 100 + DAY ( AsAtDate )
VAR Cutoff = AsAtDate - 56
VAR CutoffKey = YEAR ( Cutoff ) * 10000 + MONTH ( Cutoff ) * 100 + DAY ( Cutoff )
RETURN CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), REMOVEFILTERS ( DimDate ), FactComplaint[ReceivedDateKey] < CutoffKey, FactComplaint[FinalResponseDateKey] > AsAtKey )
```
Open complaints older than 8 weeks at the as-at date.

### Outcomes

**Uphold Rate %** · `0.0%`
```dax
Uphold Rate % =
VAR Resolved = CALCULATE ( [Complaints Resolved], FactComplaint[Outcome] <> "Withdrawn" )
VAR Upheld = CALCULATE ( [Complaints Resolved], FactComplaint[IsUpheld] = TRUE () )
RETURN IF ( Resolved > 0, DIVIDE ( Upheld, Resolved ) )
```
Fully or partially upheld complaints as a share of resolved, excluding withdrawn.

**Redress Paid** · `£#,0`
```dax
Redress Paid =
CALCULATE ( SUM ( FactComplaint[RedressAmount] ), USERELATIONSHIP ( FactComplaint[FinalResponseDateKey], DimDate[DateKey] ), FactComplaint[IsUpheld] = TRUE () )
```
Redress in GBP on upheld complaints, by final response date.

### Customers

**Active Customers** · `#,0`
```dax
Active Customers =
CALCULATE ( SUM ( FactActiveCustomers[ActiveCustomers] ), LASTNONBLANK ( DimDate[Date], CALCULATE ( SUM ( FactActiveCustomers[ActiveCustomers] ) ) ) )
```
Active customers at the latest month end in context.

**Complaints per 1,000 Customers** · `0.00`
```dax
Complaints per 1,000 Customers =
IF (
    ISFILTERED ( DimChannel ) || ISFILTERED ( DimRootCause ) || ISFILTERED ( DimTeam ) || ISFILTERED ( DimHandler ),
    BLANK (),
    AVERAGEX (
        VALUES ( DimDate[YearMonthNumber] ),
        VAR Cust = [Active Customers]
        RETURN IF ( Cust > 0, DIVIDE ( [Complaints Received], Cust ) * 1000 )
    )
)
```
Average monthly complaints per 1,000 active customers; blank below product-line level.

### Ombudsman

**FOS Referrals** · `#,0`
```dax
FOS Referrals =
CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), USERELATIONSHIP ( FactComplaint[FOSReferredDateKey], DimDate[DateKey] ) )
```
Complaints referred to the Financial Ombudsman Service in the period.

**Ombudsman Referral Rate %** · `0.0%`
```dax
Ombudsman Referral Rate % =
VAR Resolved = [Complaints Resolved]
RETURN IF ( Resolved > 0, DIVIDE ( [FOS Referrals], Resolved ) )
```
Ombudsman referrals as a share of final responses in the period.

### Labels

**Selected Period Label** · `Text`
```dax
Selected Period Label =
VAR MinD = MIN ( DimDate[Date] )
VAR MaxD = MIN ( MAX ( DimDate[Date] ), TODAY () )
RETURN IF ( FORMAT ( MinD, "yyyymm" ) = FORMAT ( MaxD, "yyyymm" ), FORMAT ( MaxD, "MMMM yyyy" ), FORMAT ( MinD, "d MMM yyyy" ) & " to " & FORMAT ( MaxD, "d MMM yyyy" ) )
```
Selected period label for page subtitles.

### RAG

**Complaints Received RAG** · `0`
```dax
Complaints Received RAG =
VAR _Value = [Complaints Received]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 1200, 1,    -- Green
        _Value <= 1320, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 1,200), 2 Amber (≤ 1,320), 3 Red (> 1,320). Drives conditional formatting for Complaints Received.

**Resolved Within 8 Weeks RAG** · `0`
```dax
Resolved Within 8 Weeks RAG =
VAR _Value = [Resolved Within 8 Weeks %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value >= 0.95, 1,    -- Green
        _Value >= 0.9, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≥ 95.0%), 2 Amber (≥ 90.0%), 3 Red (< 90.0%). Drives conditional formatting for Resolved Within 8 Weeks %.

**Uphold Rate RAG** · `0`
```dax
Uphold Rate RAG =
VAR _Value = [Uphold Rate %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 0.35, 1,    -- Green
        _Value <= 0.4, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 35.0%), 2 Amber (≤ 40.0%), 3 Red (> 40.0%). Drives conditional formatting for Uphold Rate %.

**Complaints per 1,000 Customers RAG** · `0`
```dax
Complaints per 1,000 Customers RAG =
VAR _Value = [Complaints per 1,000 Customers]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 2.5, 1,    -- Green
        _Value <= 3, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 2.50), 2 Amber (≤ 3.00), 3 Red (> 3.00). Drives conditional formatting for Complaints per 1,000 Customers.

**Ombudsman Referral Rate RAG** · `0`
```dax
Ombudsman Referral Rate RAG =
VAR _Value = [Ombudsman Referral Rate %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 0.05, 1,    -- Green
        _Value <= 0.07, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 5.0%), 2 Amber (≤ 7.0%), 3 Red (> 7.0%). Drives conditional formatting for Ombudsman Referral Rate %.

## 5. KPIs and RAG rules

| KPI | Definition | Value measure | RAG measure | Target | Green | Amber | Red |
|---|---|---|---|---|---|---|---|
| Complaints Received | Distinct complaints received in the month, by received date. | [Complaints Received] | [Complaints Received RAG] | 1,200 per month | ≤ 1,200 | ≤ 1,320 | > 1,320 |
| Resolved Within 8 Weeks % | Final responses sent within 56 days of receipt / all final responses. | [Resolved Within 8 Weeks %] | [Resolved Within 8 Weeks RAG] | 95% | ≥ 95.0% | ≥ 90.0% | < 90.0% |
| Uphold Rate % | Fully and partially upheld complaints / resolved complaints, excluding withdrawn. | [Uphold Rate %] | [Uphold Rate RAG] | 35% | ≤ 35.0% | ≤ 40.0% | > 40.0% |
| Complaints per 1,000 Customers | Monthly complaints received / active customers at month end x 1,000. | [Complaints per 1,000 Customers] | [Complaints per 1,000 Customers RAG] | 2.5 | ≤ 2.50 | ≤ 3.00 | > 3.00 |
| Ombudsman Referral Rate % | Complaints referred to the Ombudsman / final responses in the period. | [Ombudsman Referral Rate %] | [Ombudsman Referral Rate RAG] | 5% | ≤ 5.0% | ≤ 7.0% | > 7.0% |

RAG measures return 1 (green #15803D), 2 (amber #B45309) or 3 (red #B91C1C). Use conditional formatting by rules on the RAG measure. Status is never shown by colour alone: pair each colour with an icon shape or the word.

## 6. Report design

### Page 1: Executive Summary
_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.

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Complaints KPIs | kpi strip | values: [Complaints Received]; values: [Resolved Within 8 Weeks %]; values: [Uphold Rate %]; values: [Complaints per 1,000 Customers]; values: [Ombudsman Referral Rate %] | Each card shows a RAG icon and status word from its KPI's RAG measure, e.g. [Uphold Rate RAG]; Subtitle shows the prior-year value from the matching PY measure |
| Complaints Received vs Target and Prior Year | line | axis: DimDate[MonthYear]; values: [Complaints Received]; values: [Complaints Received PY]; values: [Complaints Received Target]; tooltip: [Complaints Received vs Target %] | Target as a dashed grey series; Data label on the last point only; Markers coloured by [Complaints Received RAG] with status word in the tooltip; Filter: Last 13 complete months |
| Resolved Within 8 Weeks % by Month | column | axis: DimDate[MonthYear]; values: [Resolved Within 8 Weeks %]; tooltip: [Resolved Within 8 Weeks % PY]; tooltip: [Resolved Within 8 Weeks Variance] | Constant line at 95% labelled Target; Column colour from [Resolved Within 8 Weeks RAG], with an icon in the data label; Filter: Last 6 complete months |
| Uphold Rate % by Product Line | bar | axis: DimProductLine[ProductLine]; values: [Uphold Rate %]; tooltip: [Uphold Rate % PY]; tooltip: [Complaints Resolved] | Bar colour from [Uphold Rate RAG], with a status icon in the data label; Constant line at 35%; Sorted descending; Drill-through to Team Operations |
| Complaints per 1,000 Customers by Product Line | bar | axis: DimProductLine[ProductLine]; values: [Complaints per 1,000 Customers]; tooltip: [Active Customers]; tooltip: [Complaints per 1,000 Customers PY] | Bar colour from [Complaints per 1,000 Customers RAG], with a status icon in the data label; Constant line at 2.5; Sorted descending |

### Page 2: Root Cause, Redress and Ombudsman
_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)

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Top 10 Root Causes | bar | axis: DimRootCause[RootCause]; values: [Complaints Received]; tooltip: [Complaints Received PY]; tooltip: DimRootCause[RootCauseCategory] | Sorted descending; Data labels in whole numbers; Filter: Top 10 by [Complaints Received] |
| Redress Paid by Root Cause Category | bar | axis: DimRootCause[RootCauseCategory]; values: [Redress Paid]; tooltip: [Uphold Rate %] | Data labels in £k; Sorted descending |
| Complaints by Product and Channel | matrix | rows: DimProductLine[ProductLine]; columns: DimChannel[Channel]; values: [Complaints Received] | Background colour scale from white to #1E3A8A on [Complaints Received]; Row and column totals shown |
| Ombudsman Outcomes | donut | legend: FactComplaint[FOSOutcome]; values: [FOS Referrals] | Data labels show count and percentage; Excludes 'Not referred'; Filter: FactComplaint[FOSOutcome] is not 'Not referred' |
| Ombudsman Referral Rate % Trend | line | axis: DimDate[MonthYear]; values: [Ombudsman Referral Rate %]; values: [Ombudsman Referral Rate % PY]; tooltip: [FOS Referrals] | Constant line at 5% labelled Target; Markers coloured by [Ombudsman Referral Rate RAG], with status word in the tooltip; Filter: Last 13 complete months |

### Page 3: Team Operations
_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

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Team Position Today | kpi strip | values: [Open Complaints]; values: [Open Past 8 Weeks]; values: [Complaints Resolved]; values: [Resolved Within 8 Weeks %] | [Resolved Within 8 Weeks %] card shows icon and word from [Resolved Within 8 Weeks RAG]; [Open Past 8 Weeks] card shows a warning icon when above 0 |
| Open Complaints by Ageing Band | stacked column | axis: FactComplaint[AgeingBand]; values: [Open Complaints]; legend: FactComplaint[VulnerableFlag] | Axis sorted by AgeingBandSort; Data labels on totals; Filter: FactComplaint[AgeingBand] is not 'Resolved' |
| Open Past 8 Weeks by Handler | bar | axis: DimHandler[HandlerName]; values: [Open Past 8 Weeks]; tooltip: [Open Complaints] | Sorted descending; Data labels on; Filter: [Open Past 8 Weeks] greater than 0 |
| Resolved Within 8 Weeks % by Handler | bar | axis: DimHandler[HandlerName]; values: [Resolved Within 8 Weeks %]; tooltip: [Complaints Resolved] | Bar colour from [Resolved Within 8 Weeks RAG], with an icon in the data label; Constant line at 95%; Sorted ascending; Filter: Final responses in the last 30 days |
| Open Case List | table | values: FactComplaint[ComplaintID]; values: DimProductLine[ProductLine]; values: DimRootCause[RootCause]; values: DimHandler[HandlerName]; values: FactComplaint[AgeingBand]; values: FactComplaint[VulnerableFlag]; values: [Open Complaints] | Sorted by AgeingBand, oldest first; 'Over 8 weeks' rows show a warning icon and the band text; Filter: [Open Complaints] = 1 |

### Theme

| Role | Colours |
|---|---|
| Data colours (in order) | #1E3A8A, #2B4FBF, #5577D6, #8AA2E3, #BFCDF0, #9CA3AF |
| Good / neutral / bad | #15803D, #B45309, #B91C1C |
| Text / background | #1F2937, #FFFFFF |
| Font | Segoe UI |

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

## 8. Governance: KPI register, lineage and reconciliation

### KPI definition register

| KPI | Business definition | Measure | Data source | Owner | Frequency | Thresholds | Threshold rationale |
|---|---|---|---|---|---|---|---|
| Complaints Received | Distinct complaints received in the month, by received date. | [Complaints Received] | Dynamics 365 Customer Service | Head of Complaints | Monthly, at the Customer Committee | Green ≤ 1,200 · Amber ≤ 1,320 · Red > 1,320 | Green is set at last year's monthly average; amber allows 10% headroom for seasonal peaks. |
| Resolved Within 8 Weeks % | Final responses sent within 56 days of receipt / all final responses. | [Resolved Within 8 Weeks %] | Dynamics 365 Customer Service | Head of Complaints Operations | Monthly at the Customer Committee; daily by team leaders | Green ≥ 95.0% · Amber ≥ 90.0% · Red < 90.0% | After 8 weeks the customer can refer to the Ombudsman, so tolerance for missing the deadline is low. |
| Uphold Rate % | Fully and partially upheld complaints / resolved complaints, excluding withdrawn. | [Uphold Rate %] | Dynamics 365 Customer Service | Chief Customer Officer | Monthly, at the Customer and Risk Committees | Green ≤ 35.0% · Amber ≤ 40.0% · Red > 40.0% | Set at the industry median, with amber reflecting the board's Consumer Duty risk appetite. |
| Complaints per 1,000 Customers | Monthly complaints received / active customers at month end x 1,000. | [Complaints per 1,000 Customers] | Dynamics 365 and SQL Server customer warehouse | Chief Customer Officer | Monthly, at the Customer Committee | Green ≤ 2.50 · Amber ≤ 3.00 · Red > 3.00 | Benchmarked against peer rates in the FCA complaints data and last year's 2.6 average. |
| Ombudsman Referral Rate % | Complaints referred to the Ombudsman / final responses in the period. | [Ombudsman Referral Rate %] | Dynamics 365 Customer Service | Head of Complaints Quality Assurance | Monthly, at the Risk Committee | Green ≤ 5.0% · Amber ≤ 7.0% · Red > 7.0% | Referrals above 7% signal poor final responses and rising Ombudsman case fees, per risk appetite. |

### Data lineage

| KPI | Lineage |
|---|---|
| Complaints Received | Complaints case management → Bronze → Silver → Gold → Power BI semantic model → FactComplaint → [Complaints Received] |
| Resolved Within 8 Weeks % | Complaints case management → Bronze → Silver → Gold → Power BI semantic model → FactComplaint → [Resolved Within 8 Weeks %] |
| Uphold Rate % | Complaints case management → Bronze → Silver → Gold → Power BI semantic model → FactComplaint → [Uphold Rate %] |
| Complaints per 1,000 Customers | Complaints case management → Customer data warehouse → Bronze → Silver → Gold → Power BI semantic model → FactComplaint → FactActiveCustomers → [Complaints per 1,000 Customers] |
| Ombudsman Referral Rate % | Complaints case management → Bronze → Silver → Gold → Power BI semantic model → FactComplaint → [Ombudsman Referral Rate %] |

### Reconciliation checks

| # | Check | Report measure | Compared with | Period | Tolerance |
|---|---|---|---|---|---|
| 1 | Complaints received in the month equal cases created in Dynamics 365 with a received date in that month. | [Complaints Received] | Dynamics 365 Customer Service case view | Each calendar month | Exact match |
| 2 | Final responses in the month equal Dynamics cases with a final response date in that month. | [Complaints Resolved] | Dynamics 365 Customer Service case view | Each calendar month | Exact match |
| 3 | Open complaints at month end equal the Dynamics open-case control report as at the same date. | [Open Complaints] | Dynamics 365 open complaints control report | Each month end | Exact match |
| 4 | Redress paid on upheld complaints agrees to the finance ledger redress account. | [Redress Paid] | General ledger complaints redress account | Each calendar month | Within 0.5% |
| 5 | Active customers by product line agree to the customer warehouse month-end control totals. | [Active Customers] | SQL Server customer warehouse control table | Each month end | Exact match |
| 6 | Ombudsman referrals agree to the Financial Ombudsman Service case notifications log. | [FOS Referrals] | FOS notifications log maintained by Complaints QA | Each calendar month | Within 2 cases |

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