Validation, Security and Governance

Introduction

A Power BI solution is valuable only when users can trust its numbers, understand its meaning, access only appropriate information, and use it responsibly. Data modelling and DAX therefore extend beyond creating relationships and measures. A professional model must be validated, secured, documented, governed and monitored throughout its life cycle.

Validation asks whether the model produces correct and reconcilable results. Security asks who may see which data and which model objects. Governance establishes the standards, responsibilities and controls that make the solution reliable and sustainable. These concerns are strategic because a reporting error, inappropriate disclosure or poorly controlled metric can affect financial decisions, regulatory compliance and organisational reputation.

Learning Objectives

By the end of this chapter, you should be able to:

• validate DAX calculations and reconcile totals with authoritative sources;
• distinguish data, model, report and visual validation;
• implement and test row-level security (RLS);
• explain object-level security (OLS) and its limitations in Power BI;
• apply naming, documentation and lineage standards;
• design practical access-control and governance processes;
• recognise privacy, ethical and regulatory risks in business intelligence.

1. Validation as an Evidence-Based Process

Validation is the systematic process of determining whether data, calculations and presentation satisfy defined requirements. It is not the same as merely checking whether a visual looks plausible. A plausible number can still be wrong because of duplicated fact rows, an incorrect filter relationship, an inappropriate aggregation or a measure that behaves differently at different granularities.

A useful validation chain is:

1. Source validation – confirm completeness, data types, units, dates and source-system definitions.
2. Transformation validation – verify Power Query steps, joins, filters, type conversions and refresh outcomes.
3. Model validation – inspect table grain, keys, relationships, cardinality and filter direction.
4. Calculation validation – test measures under controlled filter contexts.
5. Reconciliation – compare results with an approved ledger, operational report or independently calculated control total.
6. Presentation validation – confirm that titles, labels, filters, units and visual interactions communicate the result accurately.
7. Operational validation – test refresh schedules, permissions, deployment and failure alerts.

Validation should be risk-based. A revenue measure used for executive reporting requires stronger evidence than a temporary exploratory chart. Every important metric should have an owner, definition, source, refresh expectation and acceptance rule.

1.1 Reconciliation and control totals

Reconciliation compares two representations of the same business quantity. For a measure such as revenue, a simple difference is:

The difference is zero when exact equality is required. For values subject to rounding or timing differences, an absolute tolerance may be specified:

Here, the vertical bars denote absolute value. The tolerance must be defined in advance, with its currency, time period and materiality rationale. A percentage variance can be calculated as:

This percentage is meaningful only when the control total is non-zero and both totals use the same scope, currency, tax treatment, calendar and recognition rules. A zero control total makes the percentage undefined; in that case report the absolute difference or use an agreed alternative.

For example, if a report shows £1,248,500 and the approved control total is £1,250,000:

• Difference = £1,248,500 − £1,250,000 = −£1,500.
• Variance percentage = −£1,500 ÷ £1,250,000 × 100 = −0.12%.

The report is lower than the control by £1,500, or 0.12%. The result is acceptable only if the documented tolerance permits it.

1.2 Testing DAX measures

DAX measures should be tested with small, known datasets before being trusted in a large model. Test at least:

• one row and multiple rows;
• a single date and a date range;
• one category and all categories;
• blank, zero and missing values;
• totals and subtotals;
• non-additive measures such as ratios, averages and distinct counts;
• filters from each relevant dimension;
• security-restricted views.

A measure is evaluated in filter context. A total is not necessarily the sum of the visible rows. For example, a margin percentage should generally be calculated from total profit divided by total sales, not by adding row-level percentages.

Profit Margin % =
DIVIDE ( [Profit], [Sales] )

DIVIDE is preferable to the / operator when the denominator may be zero or blank because it can return a controlled alternate result. If the business requires zero rather than blank, this can be written as:

Profit Margin % =
DIVIDE ( [Profit], [Sales], 0 )

The choice between blank and zero is semantic: blank means “not available or not meaningful,” whereas zero means “measured quantity is zero.” It should be documented rather than chosen merely for visual appearance.

A controlled test table can include expected results:

The third result may be technically consistent with the DAX but inconsistent with the reporting requirement. Validation therefore tests both computational correctness and business meaning.

1.3 Common causes of incorrect totals

Incorrect totals often arise from:

• duplicate keys in a dimension;
• fact tables joined at incompatible grains;
• many-to-many relationships used without a clear bridge design;
• bidirectional filtering producing unexpected paths;
• inactive date relationships not activated when required;
• measures using ALL or REMOVEFILTERS too broadly;
• counting rows instead of distinct business entities;
• currency or unit conversions applied at the wrong level;
• totals combining values that should be recalculated, such as averages;
• refreshes that partially loaded or omitted late-arriving records.

A useful diagnostic approach is to create temporary measures such as row counts, distinct key counts, minimum and maximum dates, and reconciled subtotals. Tools such as Performance Analyzer, DAX Studio and Tabular Editor can support investigation, but they do not replace business-owner approval.

2. Security Architecture in Power BI

Security should be designed before publication. It includes identity, workspace roles, dataset permissions, report sharing, sensitivity labels, gateway access and data-level restrictions. Security is layered: a secure report can still expose data through an insecure semantic model or export permission.

2.1 Workspace and item permissions

Power BI workspace roles commonly distinguish administrative control from content use. Depending on the organisation’s configuration, roles such as Administrator, Member, Contributor and Viewer provide different abilities to manage, edit, publish or consume content. These roles should be assigned to groups rather than individuals wherever possible.

The principle of least privilege means that a user receives only the access necessary for their role. A person who only consumes a report should not automatically receive permission to edit the semantic model or download underlying data. Access should be reviewed periodically, especially after job changes and departures.

2.2 Row-level security

Row-level security restricts rows returned to a user according to an RLS role. It does not delete rows from the model; it changes the filter context applied when the user queries the semantic model.

Suppose a model contains a Sales fact table, a Region dimension and a UserRegion table with columns UserEmail and RegionKey. A dynamic role can be defined on the region table:

[UserEmail] = USERPRINCIPALNAME()

This expression is normally applied to a user-access table, with relationships propagating the allowed region keys to the Region and Sales tables. A more explicit pattern is:

VAR CurrentUser = USERPRINCIPALNAME()
RETURN
    'UserRegion'[UserEmail] = CurrentUser

The exact expression depends on the table to which the role is applied and the relationship design. USERPRINCIPALNAME() identifies the signed-in principal in the service; test it in the actual deployment environment because identities may differ between Desktop, service and embedded scenarios.

A static role may use a rule such as:

'Region'[RegionKey] IN { 10, 20 }

Static roles are suitable for small, stable groups but become difficult to maintain as membership changes. Dynamic RLS is usually more scalable because membership is stored as governed data rather than embedded in many role definitions.

#### RLS design principles

• Keep the security mapping table at a clear grain: one user-region permission per row.
• Use stable identifiers rather than display names.
• Prefer a single, understandable filter path to the fact table.
• Avoid unnecessary bidirectional relationships.
• Test users who have one, several and no permitted regions.
• Test totals, drill-through, exports and subscriptions.
• Document whether managers inherit access to subordinate units.
• Treat RLS as a confidentiality control, not merely a convenience filter.

RLS can be bypassed by users with sufficiently powerful workspace or model permissions. Therefore, assigning a user to an RLS role does not compensate for granting that user unrestricted edit or build access. Security testing must use representative consumer accounts.

2.3 Object-level security

Object-level security restricts access to model objects such as tables and columns. It can be used to hide sensitive columns, for example salary, personal identifiers or commercially confidential attributes, from particular roles. OLS is configured through appropriate modelling tools and role definitions; it is not implemented by simply hiding a field in the Fields pane.

A hidden column is not necessarily secure. Hiding affects discoverability, whereas OLS controls access. Similarly, removing a visual from a report does not secure the underlying data if users can query the semantic model. OLS should be tested with the intended role and with tools or interfaces that can reveal metadata.

OLS and RLS solve different problems:

3. Documentation, Naming and Lineage

Governance makes knowledge about the model explicit. Without documentation, users reconstruct definitions from visuals, and different teams create conflicting versions of “sales,” “customer” or “active employee.”

3.1 Naming standards

Names should be readable, consistent and stable. A practical standard is:

• use singular, business-friendly dimension names such as Customer and Product;
• use descriptive fact names such as Sales or InventorySnapshot;
• use explicit measure names such as Net Sales and Gross Margin %;
• avoid unexplained abbreviations and technical prefixes in user-facing fields;
• use a consistent date table name, such as Date;
• distinguish measures from columns through conventions, display folders or model metadata;
• use a standard format for currency, percentages, dates and decimal precision;
• hide surrogate keys and technical columns from ordinary report authors where appropriate.

Naming is not cosmetic. It reduces interpretation errors and improves discoverability, training and maintenance.

3.2 Measure documentation

Every critical measure should record its business definition, formula owner, source fields, grain, filters, refresh timing, units, treatment of blanks and known limitations. A concise catalogue might contain:

Descriptions can be added to model measures, while a broader catalogue can be maintained in a governed documentation repository. Documentation should be version-controlled and updated as part of change management, not after an incident.

3.3 Data lineage

Lineage describes how data travels from source systems through transformations and semantic models to reports and downstream outputs. It helps answer:

• Which reports depend on this table or measure?
• Which source field produced this business metric?
• What will be affected if a column changes?
• Where did an exported number originate?

Lineage is especially important for impact analysis, audit, incident response and regulatory inquiries. It should include source ownership, transformation steps, refresh frequency, gateway or connection information, downstream reports and certified status. Automated lineage views are useful but may not capture manual spreadsheets, external extracts or undocumented transformations; human review remains necessary.

4. Privacy, Access Control and Ethical Use

Business data can include personal, financial, health, location and behavioural information. Governance must therefore address purpose limitation, data minimisation, accuracy, retention, transparency and lawful access. Applicable law varies by jurisdiction, but privacy principles should be incorporated into design rather than treated as a final compliance check.

Practical controls include:

• collect only fields needed for a stated business purpose;
• mask or pseudonymise identifiers where individual identity is unnecessary;
• restrict sensitive columns through OLS and workspace permissions;
• define retention and deletion schedules;
• apply sensitivity labels and encryption where available;
• monitor downloads, sharing and unusual access patterns;
• separate development data from production personal data;
• document lawful basis, consent or other authority where relevant;
• establish procedures for correcting inaccurate data.

Ethical use extends beyond legal compliance. A technically permitted analysis may still be unfair, excessive or misleading. For example, using employee activity data to infer performance without explaining the measure can create unjustified surveillance. A dashboard should make uncertainty, exclusions and limitations visible. Analysts should ask whether a metric is a fair proxy, whether affected people could understand its use and whether a less intrusive dataset would achieve the same purpose.

AI-assisted features can accelerate report creation and summarisation, but generated descriptions or insights may be inaccurate, disclose sensitive information or reproduce bias. Human review, approved data boundaries and auditability are required before AI-generated content influences a consequential decision.

5. Governance Operating Model

A workable governance model allocates responsibility. A data owner defines meaning and approves access. A data steward maintains quality, metadata and definitions. A platform administrator manages capacity, workspaces and technical controls. A report developer implements the model. A business approver accepts the report for its intended use. Security and privacy specialists advise on risk and compliance.

A governance life cycle can be organised as follows:

1. Design – define purpose, users, data classification, metrics and risks.
2. Build – apply modelling, naming, documentation and security standards.
3. Validate – perform technical tests, reconciliations and user acceptance testing.
4. Approve – record owners, sensitivity, certification status and release decision.
5. Operate – monitor refresh, usage, failures, permissions and data quality.
6. Review – reassess definitions, access and continuing business need.
7. Retire – archive or remove obsolete content and revoke access.

A change-control record should identify the change, reason, affected objects, test evidence, approver, deployment date and rollback approach. Certified datasets should have stronger evidence and ownership than personal or exploratory datasets.

6. Illustrative Case: Regional Sales and Personnel Data

The following is a fictional illustrative case.

Background

Northstar Wholesale operates in four regions. Its Power BI sales model combines invoices, products, customers, regions and an employee table containing salary bands. Regional managers need sales and margin information for their own regions. The finance director needs company-wide totals but not individual salary details. The model previously contained a report filter labelled “Region,” but users could remove it and view all sales.

Problem

The organisation also found that the dashboard’s annual sales total was £2.4 million below the finance ledger. Investigation showed that a many-to-many customer relationship duplicated some invoice amounts. In addition, the report used a hidden salary column rather than actual object-level security.

Analysis

The team first established the grain of each table. The invoice table was one row per invoice line, while the customer mapping included multiple rows per customer because of historical assignments. The mapping was therefore not a valid one-to-many dimension. The team created a governed customer dimension with one current key per customer and moved historical assignment logic into a separate bridge with documented date rules.

They then reconciled monthly totals between the model and the ledger. After correcting the relationship and aligning the report’s date and tax definitions with finance, the difference fell to £0. The team documented the reconciliation procedure and tolerance for future periods.

Approach and solution

Dynamic RLS was implemented using a UserRegion table maintained by the data owner. Relationships propagated permitted region keys to sales. Salary-related columns were placed in a restricted model perspective and protected with OLS for roles that did not require them. Workspace access was changed so most managers became viewers rather than contributors. A finance-approved measure catalogue defined sales, returns, gross profit and margin.

The team tested:

• a manager assigned to one region;
• a manager assigned to two regions;
• a new user with no mapping;
• the finance director;
• export and drill-through behaviour;
• refresh failure and access removal.

Outcome

The model reconciled to the ledger, regional managers saw only permitted sales, and general users could not query salary columns. The organisation also introduced quarterly access reviews, a lineage register and a release checklist.

Learning points

The case demonstrates that a visual filter is not security, a plausible total is not validation, and a technically correct model is not governed until ownership, documentation and operating controls are established.

7. Challenges and Limitations

Security can become difficult when organisational structures change frequently, users belong to many groups or data is combined across jurisdictions. Complex RLS may reduce query performance and make debugging difficult. OLS can protect model objects but cannot correct insecure source extracts or screenshots. Export permissions and downstream files may create copies outside the original control boundary.

Reconciliation can also be misleading when the two systems use different definitions, time zones, currencies or recognition policies. Matching totals does not prove that every row is correct; offsetting errors may cancel. Sampling, exception analysis and dimensional checks are therefore needed in addition to total comparison.

Governance introduces effort and may be resisted as bureaucracy. The appropriate response is proportionality: apply stronger controls to sensitive, widely used and decision-critical assets, while preserving an efficient path for low-risk exploration. Governance should enable trustworthy self-service, not eliminate it.

Best-Practice Checklist

Before publishing a governed Power BI solution, confirm that:

• business definitions and owners are recorded;
• source, transformation and model tests have passed;
• critical totals reconcile to an authorised control source;
• totals and non-additive measures have been checked;
• RLS has been tested with representative accounts;
• OLS or equivalent controls protect sensitive objects;
• workspace, build, export and sharing permissions follow least privilege;
• names, formats, descriptions and display folders are consistent;
• lineage, sensitivity and refresh information are available;
• privacy, retention and ethical-use risks have been assessed;
• approval, version and rollback records exist;
• access and quality will be reviewed after release.

Chapter Summary

Validation establishes whether a Power BI solution is accurate, complete and fit for purpose. It combines source checks, model inspection, DAX testing, reconciliation and presentation review. Differences should be interpreted against defined scope, units and tolerances; matching totals alone cannot guarantee row-level correctness.

RLS restricts rows according to a user or role, while OLS restricts tables or columns. Neither replaces workspace permissions, export controls or least-privilege access. Dynamic RLS is scalable when supported by a well-designed user-to-entity mapping table, but it must be tested in the deployed environment.

Governance provides the organisational framework for trustworthy BI. Naming standards, measure definitions, lineage, ownership, change control, privacy safeguards and ethical review reduce ambiguity and risk. A sustainable solution is not merely a collection of visuals: it is an accountable information product whose calculations, access rules and intended use can be explained and evidenced.

Key Terms

Access control: Rules determining who may use, modify or distribute an information asset.

Business definition: An agreed meaning and scope for a metric or data element.

Control total: An authoritative value used to reconcile a report or model.

Data owner: Person accountable for the meaning, quality and authorised use of data.

Data steward: Person responsible for metadata, quality processes and day-to-day governance.

Dynamic RLS: Row-level security that determines access from the signed-in user or related membership data.

Least privilege: Providing only the access necessary to perform an approved task.

Lineage: The documented path from source data through transformations to reports and outputs.

Materiality: The significance of an error or omission in relation to a decision or user need.

Measure catalogue: A governed register of metric definitions, formulas, owners and rules.

Object-level security: Security restricting access to model tables or columns.

Privacy: Protection of people’s information and appropriate control of its collection and use.

Reconciliation: Comparison of a reported value with an authorised independent value.

Row-level security: Security restricting which rows a user can query.

Semantic model: The tables, relationships, measures and metadata used to represent business data.

Sensitivity label: A classification identifying handling requirements for information.

Tolerance: A predefined acceptable difference between reported and control values.

Validation: Evidence-based assessment that data, calculations and outputs meet requirements.

Review Questions

Short Answer Questions

1. Why is reconciliation necessary even when a dashboard appears plausible?
2. What is the difference between row-level security and object-level security?
3. State two risks of using a hidden column as a security control.
4. What information should a critical measure’s documentation contain?
5. Explain the principle of least privilege.

Long Answer Questions

1. Describe a complete validation process for a Power BI semantic model, from source checks to operational review.
2. Explain how dynamic RLS can be designed using a user-to-region mapping table, including important testing requirements.
3. Discuss how naming standards, documentation and lineage improve the reliability of self-service BI.
4. Analyse the relationship between privacy, access control and ethical use of business data.
5. Explain why reconciliation to a control total does not prove that every transaction in a model is correct.

Analytical / Application Questions

1. A regional manager can remove a report-level region filter and see company-wide sales. Diagnose the control failure and propose a secure design.
2. A model’s sales total is 0.12% below the finance ledger. Identify the questions and tests required before deciding whether the difference is acceptable.
3. Design a governance checklist for a Power BI dataset containing customer contact details and employee compensation information.

Multiple Choice Questions with Answers

Question 1

Which control restricts the rows returned to a user according to that user’s permitted region?

A. Object-level security

B. Row-level security

C. A display folder

D. A sensitivity label

Correct Answer: B. Row-level security

Question 2

Why is DIVIDE commonly preferred to the division operator in a percentage measure?

A. It automatically creates a date table

B. It prevents all filter context

C. It can handle a zero or blank denominator in a controlled way

D. It converts percentages to currency

Correct Answer: C. It can handle a zero or blank denominator in a controlled way

Question 3

A report total is £1,248,500 and the control total is £1,250,000. What is the absolute difference?

A. £500

B. £1,000

C. £1,500

D. £2,500

Correct Answer: C. £1,500

Question 4

Which statement about a hidden column is correct?

A. It always provides object-level security

B. It prevents authorised users from querying the model

C. It improves discoverability but is not, by itself, a security control

D. It automatically applies row-level filters

Correct Answer: C. It improves discoverability but is not, by itself, a security control

Question 5

What is the principal purpose of a lineage record?

A. To increase colour choices in reports

B. To show how data moves from sources through models to outputs

C. To replace all user acceptance testing

D. To calculate profit margins

Correct Answer: B. To show how data moves from sources through models to outputs

Question 6

Which is the strongest example of least privilege?

A. Giving every employee contributor access for convenience

B. Giving report consumers only the permissions needed to view approved content

C. Allowing users to download all underlying data

D. Using one shared administrator account

Correct Answer: B. Giving report consumers only the permissions needed to view approved content

Question 7

A percentage variance using a control total of zero is problematic because the calculation is:

A. Always equal to 100%

B. Undefined due to division by zero

C. Always equal to zero

D. A valid currency conversion

Correct Answer: B. Undefined due to division by zero

Question 8

Which table design best supports dynamic regional RLS?

A. A table containing one user-region permission per row

B. A table containing only report titles

C. A table containing duplicated invoice lines

D. A table containing chart colours

Correct Answer: A. A table containing one user-region permission per row

Question 9

Why should non-additive measures such as margin percentage be tested at totals and subtotals?

A. Their totals are always the sum of row percentages

B. They may need to be recalculated from aggregated components

C. They cannot respond to filters

D. They are unaffected by data quality

Correct Answer: B. They may need to be recalculated from aggregated components

Question 10

Which practice most directly supports responsible handling of sensitive personal data?

A. Collecting every available field

B. Publishing salary columns to all viewers

C. Applying data minimisation and restricting access to a justified purpose

D. Removing measure descriptions

Correct Answer: C. Applying data minimisation and restricting access to a justified purpose