Date dimension for time-based analysis and reporting

Operational team dimension for organizational hierarchy

Owner dimension for accountability tracking

Priority classification for cases

Process stage dimension for workflow tracking

Fact table for operational cases with status, due date, and ownership

Fact table for SLA targets by team and priority

Fact table for case escalations

Count of cases currently active (New, In Progress, Resolved)

CALCULATE(
  COUNTROWS(FactCase),
  FactCase[Status] IN {"New", "In Progress", "Resolved"}
)

Percentage of cases closed within SLA target

DIVIDE(
  CALCULATE(
    COUNTROWS(FactCase),
    FactCase[Status] = "Closed",
    FactCase[ClosedDate] <= FactCase[DueDate]
  ),
  CALCULATE(
    COUNTROWS(FactCase),
    FactCase[Status] = "Closed"
  ),
  0
)

Average time to close cases in hours

AVERAGEX(
  FILTER(FactCase, FactCase[Status] = "Closed"),
  DATEDIFF(FactCase[CreatedDate], FactCase[ClosedDate], HOUR)
)

Count of open cases not yet resolved

CALCULATE(
  COUNTROWS(FactCase),
  FactCase[Status] IN {"New", "In Progress"}
)

Count of cases that have been escalated

CALCULATE(
  COUNTROWS(FactCase),
  FactCase[Escalated] = TRUE
)

Count of open cases past their SLA due date

CALCULATE(
  COUNTROWS(FactCase),
  FactCase[DueDate] < TODAY(),
  FactCase[Status] IN {"New", "In Progress"}
)

Average age of open cases in hours

AVERAGEX(
  FILTER(FactCase, FactCase[Status] IN {"New", "In Progress"}),
  DATEDIFF(FactCase[CreatedDate], TODAY(), HOUR)
)

FactCase[TeamId] → DimTeam[TeamId]

many-to-one (single)

FactCase[OwnerId] → DimOwner[OwnerId]

many-to-one (single)

FactCase[PriorityId] → DimPriority[PriorityId]

many-to-one (single)

FactCase[ProcessStageId] → DimProcessStage[StageId]

many-to-one (single)

FactSlaTarget[TeamId] → DimTeam[TeamId]

many-to-one (single)

FactSlaTarget[PriorityId] → DimPriority[PriorityId]

many-to-one (single)

FactEscalation[CaseId] → FactCase[CaseId]

many-to-one (single)

DimOwner[DirectorateId] → DimTeam[TeamId]

many-to-one (single)

Real-time updates

Standard Power BI refresh is not real-time

Workaround: Use DirectQuery for more frequent updates or streaming datasets for near real-time alerts

Complex workflow visualization

Power BI has limited workflow diagram capabilities

Workaround: Use custom visuals or simplified stage-based charts

Alerting

Power BI does not have native push alerting

Workaround: Use Power Automate or external alerting systems integrated with data

SLA targets

• Adjust target hours by priority
• Add team-specific SLA overrides
• Add seasonal SLA variations

SLA targets can be configured in FactSlaTarget table with effective date ranges

Escalation rules

• Custom escalation thresholds
• Multi-level escalation paths
• Auto-escalation triggers

Escalation logic can be implemented in DAX measures or calculated columns

Ageing buckets

• Custom age ranges
• Team-specific ageing thresholds
• Priority-based ageing

Ageing buckets can be configured in DAX using DATEDIFF functions

Data model should be built in Power BI Desktop using the defined tables and relationships

Measures should be created in a dedicated measures table for easy maintenance

Conditional formatting rules should be documented for consistency

Theme file should be created to match brand colors

Consider DirectQuery for more frequent operational updates (not real-time)

Escalation workflows should be documented separately from dashboard

Data source should be tested with synthetic data before client deployment

Refresh schedule should be configured based on operational requirements