All samples
Sample · Financial Services · Complaints MI

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.
Columns provided
15 columns supplied, and where each one lands in the model.
Source columnModel destinationNotes
ComplaintIDFactComplaint[ComplaintID]Dynamics 365 case number kept as a degenerate key; distinct-counted by all volume measures.
ReceivedDateFactComplaint[ReceivedDateKey], DimDate[Date]Converted to a yyyymmdd integer key; active relationship to DimDate; start of the 8-week clock.
LoggedDateFactComplaint[LoggedDate]Kept as a date attribute for logging-delay data quality checks; not used for the 8-week clock.
FinalResponseDateFactComplaint[FinalResponseDateKey], FactComplaint[DaysToFinalResponse]yyyymmdd key with an inactive relationship to DimDate; 99991231 when no final response has been sent yet.
ClosedDateFactComplaint[ClosedDateKey]yyyymmdd key with an inactive relationship to DimDate; 99991231 while the case is still open in Dynamics.
ProductLineDimProductLine[ProductLine], FactComplaint[ProductLineKey]Conformed with the product lines in the customer extract so complaints per 1,000 joins correctly.
ChannelDimChannel[Channel], FactComplaint[ChannelKey]Dynamics option-set labels mapped to the surrogate key.
RootCauseDimRootCause[RootCause], FactComplaint[RootCauseKey]Grouped into RootCauseCategory using the Excel root-cause taxonomy.
OutcomeFactComplaint[Outcome], FactComplaint[IsUpheld]Values: Fully upheld, Partially upheld, Not upheld, Withdrawn. IsUpheld is derived from them by the Upheld rule.
RedressAmountFactComplaint[RedressAmount]GBP, DECIMAL(18,2); null converted to 0.
FOSReferredDateFactComplaint[FOSReferredDateKey]yyyymmdd key with an inactive relationship to DimDate; 99991231 when the complaint has not been referred.
FOSOutcomeFactComplaint[FOSOutcome]Values: Upheld, Not upheld, Settled, Pending; 'Not referred' when blank.
TeamIDDimTeam[TeamID], FactComplaint[TeamKey]Team that owned the case when it was received; drives row-level security.
HandlerIDDimHandler[HandlerID], FactComplaint[HandlerKey]Current case handler, mapped to the surrogate key.
VulnerableFlagFactComplaint[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.

1Sources
Complaints case management
Dynamics 365 Customer Service (Dataverse) · Daily 01:00
Customer data warehouse
SQL Server · Monthly, working day 2
Handler and team directory
SQL Server · Daily 01:00
Root cause taxonomy
Excel / CSV on SharePoint · On change, picked up daily
2Ingestion
Microsoft Fabric Data Factory pipelines with Dataverse Link to Fabric
Incremental load on Dataverse modifiedon watermark; full reload of reference files
3Storage & transform
Bronze
Fabric Lakehouse (Delta)
Silver
Fabric Lakehouse notebooks (PySpark)
Gold
Fabric Warehouse
4Semantic model
Power BI semantic model
Import
5Consumers
Customer Committee and Risk Committee
monthly Power BI App and PDF export
Complaints team leaders
daily Power BI App, RLS-restricted
Complaints MI analysts
semantic model via Analyse in Excel

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 alertsReady by 07:00 daily; refresh failures alert the BI support Teams channel and the Complaints MI Manager.
Storage modeImport
History5 years of complaints and customer counts, covering a 13-month trend plus prior year and FCA record-keeping needs.
EnvironmentsDev, Test and Prod Fabric workspaces
DeploymentFabric deployment pipelines with deployment rules for connections
Source controlSemantic 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

Key Business key Foreign key Degenerate Numeric Hidden Sort by
DimDateDate tabledimension
Grain: One row per calendar day, plus a 99991231 placeholder row
  • DateKeyINT
  • DateDATE
  • YearINT
  • QuarterNVARCHAR(7)
    sort by YearQuarterNumber
  • YearQuarterNumberINT
  • MonthNumberINT
  • MonthNameNVARCHAR(10)
    sort by MonthNumber
  • MonthYearNVARCHAR(8)
    sort by YearMonthNumber
  • YearMonthNumberINT
  • IsWorkingDayBIT
DimChannelSCD type 1dimension
Grain: One row per complaint channel
  • ChannelKeyINT
  • ChannelNVARCHAR(30)
    Business key
DimTeamSCD type 1dimension
Grain: One row per complaints team
  • TeamKeyINT
  • TeamIDNVARCHAR(20)
    Business key
  • TeamNameNVARCHAR(50)
  • SiteNVARCHAR(50)
  • TeamLeaderUPNNVARCHAR(100)
FactComplaintfact
Grain: One row per complaint case
  • 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
FactActiveCustomersfact
Grain: One row per product line per month end
  • MonthEndDateKeyINT
    → DimDate[DateKey]
  • ProductLineKeyINT
    → DimProductLine[ProductLineKey]
  • ActiveCustomersINT
DimProductLineSCD type 1dimension
Grain: One row per product line
  • ProductLineKeyINT
  • ProductLineNVARCHAR(50)
    Business key
  • BusinessAreaNVARCHAR(50)
DimRootCauseSCD type 1dimension
Grain: One row per root cause
  • RootCauseKeyINT
  • RootCauseNVARCHAR(100)
    Business key
  • RootCauseCategoryNVARCHAR(50)
DimHandlerSCD type 1dimension
Grain: One row per case handler
  • 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.

Relationships
From (many)To (one)CardinalityFilter directionActive
FactComplaint.ReceivedDateKeyDimDate.DateKeymany-to-oneSingleYes
FactComplaint.FinalResponseDateKeyDimDate.DateKeymany-to-oneSingleInactive
FactComplaint.ClosedDateKeyDimDate.DateKeymany-to-oneSingleInactive
FactComplaint.FOSReferredDateKeyDimDate.DateKeymany-to-oneSingleInactive
FactComplaint.ProductLineKeyDimProductLine.ProductLineKeymany-to-oneSingleYes
FactComplaint.ChannelKeyDimChannel.ChannelKeymany-to-oneSingleYes
FactComplaint.RootCauseKeyDimRootCause.RootCauseKeymany-to-oneSingleYes
FactComplaint.TeamKeyDimTeam.TeamKeymany-to-oneSingleYes
FactComplaint.HandlerKeyDimHandler.HandlerKeymany-to-oneSingleYes
FactActiveCustomers.MonthEndDateKeyDimDate.DateKeymany-to-oneSingleYes
FactActiveCustomers.ProductLineKeyDimProductLine.ProductLineKeymany-to-oneSingleYes

3. DAX measures

An excerpt: the first 6 of 33 measures. Download the .dax file for all of them.

Validated: 33 measures, 0 errors, 4 warnings
Complaints Received
Complaints received in the period, by received date.
Volumes
Complaints Received =
DISTINCTCOUNT ( FactComplaint[ComplaintID] )
Complaints Received PY
Complaints received in the same period last year.
Time Intelligence
Complaints Received PY =
CALCULATE ( [Complaints Received], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
Complaints Received Monthly Target
Firm-wide monthly complaints received target.
Targets
Complaints Received Monthly Target =
1200
Complaints Received Target
Monthly target times months in context; blank when sliced below firm level.
Targets
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 )
Complaints Received vs Target %
Percentage by which received complaints differ from target.
Targets
Complaints Received vs Target % =
VAR Tgt = [Complaints Received Target]
RETURN IF ( ISBLANK ( Tgt ) || Tgt <= 0, BLANK (), DIVIDE ( [Complaints Received] - Tgt, Tgt ) )
Complaints Resolved
Complaints given a final response in the period.
Volumes
Complaints Resolved =
CALCULATE ( DISTINCTCOUNT ( FactComplaint[ComplaintID] ), USERELATIONSHIP ( FactComplaint[FinalResponseDateKey], DimDate[DateKey] ) )
Download all 33 measures

4. KPIs and RAG rules

KPITargetDirectionGreen (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,3201,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.002.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

1 Green#15803D2 Amber#B453093 Red#B91C1C

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]
Complaints MI Pack
Executive Summary
Last complete calendar month, with a 13-month trend
Complaints Received Green
1,184
-3.1%Target 1,200 per month
Resolved Within 8 Weeks % Amber
92.4%
-1.2 ptsTarget 95%
Uphold Rate % Red
41.2%
+2.6 ptsTarget 35%
Complaints per 1,000 Customers Green
2.38
-0.07Target 2.5
Ombudsman Referral Rate % Amber
5.8%
+0.4 ptsTarget 5%
Complaints Received vs Target and Prior Year
Feb 2025Apr 2025Jun 2025Aug 2025
Resolved Within 8 Weeks % by Month
Apr 2025May 2025Jun 2025Jul 2025Aug 2025Sep 2025
Uphold Rate % by Product Line
Current Accounts
108%
Credit Cards
106%
Mortgages
101%
Personal Loans
85%
Savings
74%
Home Insurance
73%
Complaints per 1,000 Customers by Product Line
Current Accounts
104%
Credit Cards
98%
Mortgages
92%
Personal Loans
88%
Savings
88%
Home Insurance
75%
Visual specification: Executive Summary
VisualTypeFieldsFormatting and interactionAnswers
Complaints KPIskpi strip
values: [Complaints Received], [Resolved Within 8 Weeks %], [Uphold Rate %], [Complaints per 1,000 Customers], [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
Are the five headline complaint KPIs on target this month?
Complaints Received vs Target and Prior Yearline
axis: DimDate[MonthYear]
values: [Complaints Received], [Complaints Received PY], [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
Is complaint volume trending above target or last year?
Resolved Within 8 Weeks % by Monthcolumn
axis: DimDate[MonthYear]
values: [Resolved Within 8 Weeks %]
tooltip: [Resolved Within 8 Weeks % PY], [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
Are we meeting the 8-week final response deadline each month?
Uphold Rate % by Product Linebar
axis: DimProductLine[ProductLine]
values: [Uphold Rate %]
tooltip: [Uphold Rate % PY], [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
Which products uphold the most complaints?
Complaints per 1,000 Customers by Product Linebar
axis: DimProductLine[ProductLine]
values: [Complaints per 1,000 Customers]
tooltip: [Active Customers], [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
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]
Complaints MI Pack
Root Cause, Redress and Ombudsman
Last 3 complete calendar months
Top 10 Root Causes
Payment processing error
104%
Mis-selling
95%
Poor service
94%
Fees and charges
90%
Delay in account closure
84%
Fraud and scams
84%
Redress Paid by Root Cause Category
Process failure
114%
Product design
110%
Staff conduct
94%
Systems
93%
Third party
83%
Communication
80%
Complaints by Product and Channel
NameActualvs Target
Current Accounts£1334K97.4%
Credit Cards£1204K87.9%
Mortgages£1091K79.6%
Personal Loans£1298K94.8%
Savings£1334K97.4%
Home Insurance£1121K81.8%
Ombudsman Outcomes
25%
  • Upheld
  • Not upheld
  • Settled
  • Pending
Ombudsman Referral Rate % Trend
Oct 2024Feb 2025Jun 2025
Visual specification: Root Cause, Redress and Ombudsman
VisualTypeFieldsFormatting and interactionAnswers
Top 10 Root Causesbar
axis: DimRootCause[RootCause]
values: [Complaints Received]
tooltip: [Complaints Received PY], DimRootCause[RootCauseCategory]
  • Sorted descending
  • Data labels in whole numbers
  • Filter: Top 10 by [Complaints Received]
Which root causes drive the most complaints?
Redress Paid by Root Cause Categorybar
axis: DimRootCause[RootCauseCategory]
values: [Redress Paid]
tooltip: [Uphold Rate %]
  • Data labels in £k
  • Sorted descending
Where is redress cost concentrated?
Complaints by Product and Channelmatrix
rows: DimProductLine[ProductLine]
columns: DimChannel[Channel]
values: [Complaints Received]
  • Background colour scale from white to #1E3A8A on [Complaints Received]
  • Row and column totals shown
Which product and channel combinations carry most complaints?
Ombudsman Outcomesdonut
legend: FactComplaint[FOSOutcome]
values: [FOS Referrals]
  • Data labels show count and percentage
  • Excludes 'Not referred'
  • Filter: FactComplaint[FOSOutcome] is not 'Not referred'
How does the Ombudsman rule on referred complaints?
Ombudsman Referral Rate % Trendline
axis: DimDate[MonthYear]
values: [Ombudsman Referral Rate %], [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
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]
Complaints MI Pack
Team Operations
As at today (latest refresh)
Complaints Received Green
1,184
-3.1%Target 1,200 per month
Resolved Within 8 Weeks % Amber
92.4%
-1.2 ptsTarget 95%
Uphold Rate % Red
41.2%
+2.6 ptsTarget 35%
Complaints per 1,000 Customers Green
2.38
-0.07Target 2.5
Ombudsman Referral Rate % Amber
5.8%
+0.4 ptsTarget 5%
Open Complaints by Ageing Band
0-3 days4-14 day15-28 da29-56 daOver 8 w
Open Past 8 Weeks by Handler
A. Patel
109%
J. Morgan
103%
S. Okafor
99%
L. Hughes
93%
R. Clarke
92%
M. Nowak
76%
Resolved Within 8 Weeks % by Handler
A. Patel
112%
J. Morgan
106%
S. Okafor
101%
L. Hughes
94%
R. Clarke
77%
M. Nowak
74%
Open Case List
NameActualvs Target
CMP-104522£1512K110.3%
CMP-104587£1272K92.8%
CMP-104611£1383K100.9%
CMP-104690£1525K111.3%
CMP-104702£1255K91.6%
Visual specification: Team Operations
VisualTypeFieldsFormatting and interactionAnswers
Team Position Todaykpi strip
values: [Open Complaints], [Open Past 8 Weeks], [Complaints Resolved], [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
How many cases are open, breaching and resolved on time?
Open Complaints by Ageing Bandstacked column
axis: FactComplaint[AgeingBand]
legend: FactComplaint[VulnerableFlag]
values: [Open Complaints]
  • Axis sorted by AgeingBandSort
  • Data labels on totals
  • Filter: FactComplaint[AgeingBand] is not 'Resolved'
How old is the open caseload?
Open Past 8 Weeks by Handlerbar
axis: DimHandler[HandlerName]
values: [Open Past 8 Weeks]
tooltip: [Open Complaints]
  • Sorted descending
  • Data labels on
  • Filter: [Open Past 8 Weeks] greater than 0
Which handlers hold cases past the 8-week deadline?
Resolved Within 8 Weeks % by Handlerbar
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
Which handlers miss the 8-week deadline most?
Open Case Listtable
values: FactComplaint[ComplaintID], DimProductLine[ProductLine], DimRootCause[RootCause], DimHandler[HandlerName], FactComplaint[AgeingBand], FactComplaint[VulnerableFlag], [Open Complaints]
  • Sorted by AgeingBand, oldest first
  • 'Over 8 weeks' rows show a warning icon and the band text
  • Filter: [Open Complaints] = 1
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

Confirm these with the business before build. Each one changes the model or measures if the answer differs.
#Assumption or questionImpact if different
1Upheld 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.
2The 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.
3Committee 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.
4The 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.
5Active 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.
6Team 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.
7RedressAmount 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.

Make one for your own report

Start free