Power BI Setup¶

Deployment and configuration guide for the Compliance Dashboard Power BI report.

Overview¶

The Compliance Dashboard provides five report pages:

Executive Summary - Overall compliance posture
Pillar Overview - Breakdown by governance pillar
Control Details - Individual control status
Exception Tracker - Open exceptions and remediation
Trend Analysis - Historical compliance trends

Prerequisites¶

Power BI Desktop (latest version)
Power BI Pro or Premium license
Dataverse tables deployed (see Dataverse Schema)
Network access to Dataverse environment

Deployment Steps¶

Step 1: Build the Template¶

Per the Solution Content Policy, this repo does not ship a .pbit file. Build your own following docs/power-bi-template-spec.md and save the resulting .pbix / .pbit to a workspace location your team controls.

Step 2: Open in Power BI Desktop¶

Open Power BI Desktop
Open your built ComplianceDashboard.pbit (or .pbix)
Template will prompt for parameters

Step 3: Configure Parameters¶

Parameter	Description	Example
`DataverseEnvironmentUrl`	Your Dataverse environment URL	`https://example.crm.dynamics.com`
`TenantId`	Your Microsoft Entra ID tenant ID	`12345678-1234-1234-1234-123456789abc`

Step 4: Authenticate¶

Click Connect
Sign in with organizational account
Select Organizational account for Dataverse connection
Click Connect

Step 5: Refresh Data¶

Click Refresh to load current data
Verify data loads successfully
Check for any error indicators

Step 6: Publish to Power BI Service¶

Click Publish
Select destination workspace
Wait for publish to complete
Click link to open in Power BI Service

Data Model¶

Tables¶

Table	Source	Refresh Mode
ControlMaster	fsi_controlmaster	Import
ControlAssessment	fsi_controlassessment	Import
ComplianceScore	fsi_compliancescore	Import
ComplianceException	fsi_complianceexception	Import
ComplianceEvidence	fsi_complianceevidence	Import
DateTable	Generated	Import

Relationships¶

DateTable ──────┬──────── ComplianceScore (fsi_scoredate)
                │
ControlMaster ──┼──────── ControlAssessment (fsi_controlmasterid)
                │
ControlAssessment ──┬──── ComplianceException (fsi_controlassessmentid)
                    │
                    └──── ComplianceEvidence (fsi_controlassessmentid)

Date Table¶

Auto-generated date table for time intelligence:

DateTable =
ADDCOLUMNS(
    CALENDAR(DATE(2025, 1, 1), DATE(2027, 12, 31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & QUARTER([Date]),
    "WeekNum", WEEKNUM([Date]),
    "DayOfWeek", WEEKDAY([Date]),
    "IsCurrentMonth", IF(MONTH([Date]) = MONTH(TODAY()) && YEAR([Date]) = YEAR(TODAY()), TRUE, FALSE)
)

Report Pages¶

Page 1: Executive Summary¶

Visuals:

Visual	Type	Data
Overall Score	Card	Latest overall score
Score Change 30D	Line chart	30-day score change (see dax-measures.md)
Pillar Scores	Column chart	Score by pillar
Zone Scores	Donut chart	Score by zone
Exception Count	Card	Open exceptions
SLA Status	Stacked bar	Exceptions by SLA status

Filters: - Date range (last 30/60/90 days)

Page 2: Pillar Overview¶

Visuals:

Visual	Type	Data
Pillar Cards	Multi-row card	Score per pillar
Control Status Matrix	Matrix	Controls by pillar × status
Pillar Trend	Line chart	Pillar scores over time
Non-Compliant Controls	Table	List of non-compliant controls

Filters: - Pillar selector - Zone selector

Page 3: Control Details¶

Visuals:

Visual	Type	Data
Control List	Table	All controls with status
Assessment History	Line chart	Selected control over time
Evidence List	Table	Evidence for selected control
Regulatory Tags	Slicer	Filter by regulation

Filters: - Pillar - Zone - Status - Regulatory reference

Drill-through: - Click control to see full assessment history

Page 4: Exception Tracker¶

Visuals:

Visual	Type	Data
Open Exceptions	Card	Count of open exceptions
SLA Distribution	Pie chart	By SLA status
Exception Table	Table	Full exception list
Age Distribution	Histogram	Days open distribution
Owner Workload	Bar chart	Exceptions per owner

Filters: - Severity - Status - Owner - SLA Status

Page 5: Trend Analysis¶

Visuals:

Visual	Type	Data
Score Change 30D	Line chart	Overall score change over time
Pillar Trends	Multi-line chart	All pillars over time
MoM Change	KPI	Month-over-month change
Forecast	Line chart	30-day forecast (optional)
Control Status Changes	Waterfall	Changes in status counts

Filters: - Date range - Pillar - Zone

DAX Measures¶

See DAX Measures for complete measure definitions.

Key Measures¶

Overall Compliance Score

Overall Score =
VAR LatestDate = MAX(ComplianceScore[fsi_scoredate])
RETURN
    CALCULATE(
        AVERAGE(ComplianceScore[fsi_overallscore]),
        ComplianceScore[fsi_scoredate] = LatestDate
    )

Score Change 30D

// Note: this measure is named "Score Change 30D" in docs/dax-measures.md.
// Use a single name across the model to avoid breaking visuals.
Score Change 30D =
VAR CurrentScore = [Overall Score]
VAR PriorScore =
    CALCULATE(
        [Overall Score],
        DATEADD(DateTable[Date], -30, DAY)
    )
RETURN
    CurrentScore - PriorScore

Exception SLA %

SLA Compliance % =
VAR OnTrack =
    CALCULATE(
        COUNTROWS(ComplianceException),
        ComplianceException[fsi_slastatus] = 1,
        ComplianceException[fsi_exceptionstatus] IN {1, 2, 3}
    )
VAR Total =
    CALCULATE(
        COUNTROWS(ComplianceException),
        ComplianceException[fsi_exceptionstatus] IN {1, 2, 3}
    )
RETURN
    DIVIDE(OnTrack, Total, 1)

Scheduled Refresh¶

Configure in Power BI Service¶

Go to dataset settings
Click Scheduled refresh
Configure:
Refresh frequency: Daily
Time: 7:00 AM (after score calculation flow)
Notify on failure: Yes

Gateway Requirements¶

If Dataverse is behind a firewall: - Install On-premises data gateway - Configure gateway connection - Map Dataverse data source

For cloud-only deployments: - No gateway required - Use organizational account authentication

Row-Level Security (RLS)¶

Pseudocode below. USERPRINCIPALNAME() returns a string and cannot be used directly inside IN { ... }. Production RLS requires loading a mapping table (UPN → Zone, UPN → Pillar) into the model and joining on it. The snippets below illustrate intent only.

Role Definitions¶

Zone Viewer (pseudocode)

// Replace with a real mapping pattern, e.g.:
// [Zone] IN CALCULATETABLE(VALUES(UserZoneMapping[Zone]),
//          UserZoneMapping[Upn] = USERPRINCIPALNAME())
[Zone] IN {USERPRINCIPALNAME() zone assignments}

Pillar Owner (pseudocode)

// Replace with a real mapping pattern, e.g.:
// [Pillar] IN CALCULATETABLE(VALUES(UserPillarMapping[Pillar]),
//          UserPillarMapping[Upn] = USERPRINCIPALNAME())
[Pillar] IN {USERPRINCIPALNAME() pillar assignments}

Implementation¶

Open report in Power BI Desktop
Click Modeling > Manage roles
Create roles with DAX filters
Publish report
In Power BI Service, assign users to roles

Customization¶

Adding Custom Controls¶

Add rows to fsi_controlmaster table in Dataverse
Refresh Power BI dataset
New controls appear automatically

Modifying Scoring Weights¶

Update fsi_weight in fsi_controlmaster
Score calculation flow uses updated weights
Refresh Power BI to see changes

Custom Visuals¶

Recommended custom visuals: - Bullet Chart - For target vs. actual scores - Chiclet Slicer - For pillar/zone selection - Card with States - For score with conditional formatting

Troubleshooting¶

Issue	Solution
No data showing	Verify Dataverse connection, check data exists
Refresh fails	Check credentials, verify network access
Slow performance	Reduce date range, use aggregations
Missing controls	Verify control master data loaded

Export Options¶

PDF - Static report snapshot
PowerPoint - Presentation format
Excel - Underlying data tables

Workspace access - Share entire workspace
Direct sharing - Share specific report
Embed - Embed in Teams or SharePoint
Publish to web - Public access (not recommended for compliance data)

Compliance Dashboard v1.0.3

Power BI Setup¶

Overview¶

Prerequisites¶

Deployment Steps¶

Step 1: Build the Template¶

Step 2: Open in Power BI Desktop¶

Step 3: Configure Parameters¶

Step 4: Authenticate¶

Step 5: Refresh Data¶

Step 6: Publish to Power BI Service¶

Data Model¶

Tables¶

Relationships¶

Date Table¶

Report Pages¶

Page 1: Executive Summary¶

Page 2: Pillar Overview¶

Page 3: Control Details¶

Page 4: Exception Tracker¶

Page 5: Trend Analysis¶

DAX Measures¶

Key Measures¶

Scheduled Refresh¶

Configure in Power BI Service¶

Gateway Requirements¶

Row-Level Security (RLS)¶

Role Definitions¶

Implementation¶

Customization¶

Adding Custom Controls¶

Modifying Scoring Weights¶

Custom Visuals¶

Troubleshooting¶

Export and Sharing¶

Export Options¶

Sharing Options¶