Power BI Foundations and Semantic Data Modelling

Introduction

Power BI is Microsoft’s platform for connecting to data, transforming it, modelling relationships, creating calculations, and communicating insights through interactive reports. For business analysts, however, Power BI is more than a chart-building tool. Its analytical reliability depends on the quality of the underlying semantic model: the business-oriented structure that defines entities, relationships, measures, filters, and shared meanings.

A visually attractive report can still be misleading if sales are duplicated by an incorrect relationship, dates are interpreted inconsistently, or a calculated column is used where a measure is required. This chapter therefore treats Power BI as a complete analytical workflow involving Power BI Desktop, Power Query, data modelling, DAX, and report design.

The central design principle is separation of responsibilities:

• Power Query prepares and shapes source data.
• The semantic model represents business entities and their relationships.
• DAX measures calculate results dynamically according to filter context.
• Reports present those results to users.

This separation improves accuracy, maintainability, performance, and governance.

Learning Objectives

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

1. Explain the role of Power BI Desktop, Power Query, the model, and reports.
2. Distinguish data preparation from semantic modelling and report calculation.
3. Design and justify a basic star schema.
4. Create and manage one-to-many relationships and filter directions.
5. Explain the importance of a dedicated date table.
6. Distinguish calculated columns, measures, and visual- or report-level calculations.
7. Write and interpret introductory DAX measures.
8. Identify modelling, governance, privacy, and performance risks.

1. The Power BI Analytical Workflow

Power BI Desktop normally supports four connected activities.

1.1 Connecting to data

Power BI can connect to spreadsheets, text files, relational databases, cloud services, web sources, and other systems. A connection may import data into the model or, in some scenarios, query the source more directly. The choice affects performance, freshness, security, and available transformations.

1.2 Transforming data with Power Query

Power Query is the data preparation environment in Power BI. It uses a sequence of transformation steps, written in the M language, to perform operations such as:

• removing unnecessary columns;
• changing data types;
• splitting or merging columns;
• filtering invalid records;
• replacing missing or inconsistent values;
• appending tables with similar structures;
• merging tables using matching keys;
• unpivoting cross-tabulated data; and
• creating reusable parameters and functions.

Power Query transformations are generally applied before data enters the model. This is valuable because cleaning data once during refresh is usually more efficient and more controlled than repeating corrective logic in every visual.

A good query should be readable as a sequence of business transformations. Steps should have meaningful names, unnecessary intermediate queries should be removed, and source-specific logic should be documented.

1.3 Building the semantic model

The model contains tables, columns, relationships, hierarchies, metadata, and calculations. It answers questions such as:

• Which table contains one row per sale?
• Which field identifies a customer?
• How does a product connect to a transaction?
• Which date should filter revenue?
• Is revenue additive across time and geography?

A semantic model is successful when users can ask business questions without needing to understand the physical structure of every source system.

1.4 Designing reports

Reports contain pages, visuals, slicers, filters, bookmarks, and interactions. Report design should expose the meaning already established in the model rather than compensate for a poorly designed model. If the same business definition is recreated independently in many visuals, inconsistencies are likely.

2. Power Query and the Data Preparation Layer

2.1 Query transformations versus model calculations

A transformation belongs in Power Query when it changes the structure or quality of data before analysis. Examples include converting a text date to a date type, standardising country names, or removing duplicate source records.

A calculation belongs in the model when it must respond dynamically to filters. For example, total sales should normally be a measure, because it must change when a user selects a region, product, or month.

The following distinction is useful:

2.2 Data types and data quality

Data types are semantic information, not merely formatting. A date stored as text cannot reliably support time intelligence. A product identifier should generally remain text even if it contains only digits, because it is a label rather than a quantity. Currency, decimal, whole number, percentage, and Boolean types should be selected according to meaning.

Common quality checks include:

• uniqueness of supposed keys;
• null or blank values;
• unexpected duplicates;
• invalid dates;
• inconsistent spelling and casing;
• negative or impossible quantities;
• duplicated transaction lines; and
• mismatched currencies or units.

A transformation that silently removes records can create an apparently clean but incomplete dataset. Therefore, row counts and exception outputs should be monitored during refresh.

2.3 Query folding

When the source system can execute a transformation, Power Query may translate the operation back to the source. This is called query folding. Folding can reduce data movement and improve refresh performance, especially for large databases. Not every transformation folds, and the folding behaviour depends on the connector and source.

A practical principle is to filter rows, select columns, and perform source-compatible operations as early as possible. Complex custom operations should be used deliberately and tested against refresh requirements.

3. Semantic Models and the Star Schema

A semantic model translates operational data into an analytical structure. The most widely recommended structure for many business intelligence solutions is the star schema.

3.1 Fact tables

A fact table records business events or measurable observations. Examples include sales lines, invoices, shipments, website sessions, and support cases. A fact table should have a clearly stated grain: the level represented by one row.

For example, the grain of a sales fact table might be “one row per product per order.” If an order has three products, it contains three fact rows. Measures such as quantity, discount, and net sales must be interpreted at that grain.

A useful sales fact table might include:

• OrderID;
• OrderDate;
• CustomerKey;
• ProductKey;
• StoreKey;
• Quantity;
• UnitPrice; and
• DiscountAmount.

The fact table usually contains foreign keys to dimensions and numeric values that can be aggregated.

3.2 Dimension tables

Dimension tables describe the entities involved in events. Typical dimensions include Date, Product, Customer, Store, Employee, and Geography. A dimension usually contains one row per distinct business entity and descriptive attributes used for filtering, grouping, and classification.

A Product dimension might contain ProductKey, ProductName, Brand, Category, and StandardCost. A Customer dimension might contain CustomerKey, Segment, City, and CustomerType.

3.3 Grain before relationships

Before creating relationships, state the grain of every table. Consider a fact table with one row per order line and a discount table with one row per order. Joining them directly on OrderID may repeat the order-level discount once for every line, causing an inflated total if the discount is summed. The correct design may require allocation, a separate measure, or a model that respects the different grains.

This is one of the most important analytical modelling rules: relationships do not correct incompatible grains.

3.4 Star schema structure

In a star schema, dimensions surround a central fact table. Relationships normally flow from the one-side dimension to the many-side fact table.

Figure: A star schema in which descriptive dimensions filter a central sales fact table.

4. Relationships and Filter Propagation

A relationship connects columns that represent the same business key. The most common relationship is one-to-many: one row in a dimension corresponds to many rows in a fact table. For example, one product can occur on many sales lines.

4.1 Relationship properties

Important properties include:

• Cardinality: one-to-one, one-to-many, or many-to-many.
• Cross-filter direction: the direction in which filters propagate.
• Active status: whether the relationship is used by default.
• Key compatibility: whether related columns have matching data types and values.

In a conventional star schema, single-direction filtering from dimension to fact is usually preferred. Bidirectional filtering can be useful in carefully designed cases but may create ambiguity, unexpected results, or slower queries.

4.2 Inactive relationships

A fact table may contain several dates, such as OrderDate, ShipDate, and DeliveryDate. One relationship to the Date table may be active, while others are inactive. A measure can activate an alternative relationship for a particular calculation using DAX functions such as USERELATIONSHIP.

For example:

Orders Shipped =
CALCULATE(
    [Order Count],
    USERELATIONSHIP('Date'[Date], Sales[ShipDate])
)

This measure does not change the model’s default date relationship; it temporarily uses the shipping-date relationship during evaluation.

4.3 Many-to-many relationships

Many-to-many relationships occur when values are not unique on either side. They may be legitimate, such as customers belonging to multiple marketing groups, but they require careful design. A bridge table containing unique combinations is often clearer than relying on an ambiguous direct relationship.

5. Date Tables and Time Analysis

A dedicated date table provides a consistent calendar for filtering and grouping. It should contain one row for every date in the required analysis range and generally include fields such as:

• Date;
• Year;
• Month Number;
• Month Name;
• Quarter;
• Week or fiscal period, where appropriate; and
• Year-Month sort fields.

The Date column should contain unique values and no unintended gaps within the analytical range. The table should be marked as a date table when required by the modelling environment, and its month names should be sorted by Month Number rather than alphabetically.

A common DAX pattern is:

Date =
ADDCOLUMNS(
    CALENDAR(DATE(2023, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Year Month", FORMAT([Date], "YYYY-MM")
)

The date range should be designed deliberately. Extending it far beyond the data may produce confusing empty periods, while restricting it may prevent valid future or historical analysis.

Time-intelligence measures depend on a valid date context. For example:

Sales YTD =
TOTALYTD([Total Sales], 'Date'[Date])

This assumes that the Date table is properly related to the fact table and that the date column is suitable for time-based filtering.

6. Calculated Columns, Measures, and Report Calculations

These calculation types are often confused, but they have different evaluation behaviour and purposes.

6.1 Calculated columns

A calculated column is computed row by row and stored in the model. For example:

Line Amount = Sales[Quantity] * Sales[Unit Price]

For each row, the formula uses values from that row. A calculated column is appropriate when the result is a stable attribute needed for grouping, filtering, sorting, or relationships.

Because calculated columns consume model memory and are calculated during refresh, they should not be created unnecessarily. If a result can be calculated dynamically as an aggregate, a measure is often preferable.

6.2 Measures

A measure is evaluated when a visual or query requests it. Its result changes with filter context. A basic measure is:

Total Sales = SUM(Sales[Line Amount])

If a report is filtered to one region, the measure returns regional sales; if filtered to one month, it returns monthly sales. The formula remains the same while the evaluation context changes.

Other common measures include:

Order Count = DISTINCTCOUNT(Sales[OrderID])

Average Order Value = DIVIDE([Total Sales], [Order Count])

Profit = [Total Sales] - [Total Cost]

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

The DIVIDE function is generally safer than using the division operator because it can handle a zero or blank denominator according to specified alternatives.

6.3 Filter context and CALCULATE

Filter context is the set of filters affecting a calculation. It may come from slicers, visual axes, page filters, report filters, relationships, and DAX expressions.

CALCULATE evaluates an expression in a modified filter context. For example:

Online Sales =
CALCULATE(
    [Total Sales],
    Sales[Channel] = "Online"
)

This measure retains the relevant surrounding context while adding the condition that Channel equals Online, subject to DAX filter behaviour.

6.4 Visual calculations and report-level logic

Some calculations can be created at the visual level, such as a running total or a difference from the previous category, depending on the Power BI feature and version. These are useful for presentation-specific analysis but may not be reusable across the entire model.

A calculation should be implemented as a reusable model measure when it represents a shared business definition, such as revenue, customer count, or gross margin. A visual-level calculation is more appropriate when it is specific to one visual’s layout or analytical question.

7. Worked Example: Building a Retail Model

Assume a retailer receives four tables:

• SalesLines: one row per product on an order;
• Products: one row per product;
• Customers: one row per customer; and
• Stores: one row per store.

The analyst first defines the grain of SalesLines as “one product line on one customer order.” Power Query removes records with missing ProductKey, converts OrderDate to a date type, and standardises store codes. It also checks that ProductKey values in SalesLines exist in Products.

The model then creates these relationships:

• Products[ProductKey] 1 → * SalesLines[ProductKey]
• Customers[CustomerKey] 1 → * SalesLines[CustomerKey]
• Stores[StoreKey] 1 → * SalesLines[StoreKey]
• Date[Date] 1 → * SalesLines[OrderDate]

Suppose the fact table contains the following valid lines:

If Discount is a line-level amount, net sales are calculated as:

Here, the summation is over all sales-line rows, Quantity is measured in units, Unit Price and Discount are currency amounts per line or line amounts as defined by the source, and all values must use the same currency. The calculation is valid only if Discount is consistently defined at the line level.

The rows produce:

• 2 × 50 − 5 = 95;
• 1 × 80 − 0 = 80; and
• 3 × 20 − 6 = 54.

Therefore, total net sales are 95 + 80 + 54 = 229 currency units.

A model measure might be:

Net Sales = SUM(SalesLines[Quantity] * SalesLines[Unit Price]) - SUM(SalesLines[Discount])

In standard DAX, multiplication across columns is more safely implemented through a row iterator when Line Amount is not already materialised:

Net Sales =
SUMX(
    SalesLines,
    SalesLines[Quantity] * SalesLines[Unit Price] - SalesLines[Discount]
)

The result changes appropriately when a user selects a product, store, customer segment, or date period.

8. Illustrative Case: Northstar Retail

Background

Northstar Retail operates 35 stores and an online channel. Its existing dashboard reports monthly revenue, but regional managers distrust the results because totals differ between the finance spreadsheet and Power BI.

Problem

Investigation reveals three issues. First, the source sales table contains repeated customer descriptions rather than a stable customer key. Second, the report uses OrderDate in some visuals and ShipDate in others without clearly distinguishing them. Third, revenue is implemented as a calculated column in several imported tables, producing duplicated totals after a merge.

Analysis

The analytics team profiles the source data in Power Query. It finds that customer names are not unique, product codes contain inconsistent data types, and one order-level promotion is repeated across several order lines. The team documents the grain of each source table before designing the model.

Approach and solution

The team creates a star schema with SalesLines as the fact table and separate Date, Product, Customer, Store, and Promotion dimensions. A bridge or allocation approach is used for the order-level promotion so it is not summed once per line. A dedicated Date table is connected to OrderDate as the active relationship, while ShipDate is connected as an inactive relationship for shipping measures.

The team creates reusable measures including Total Sales, Order Count, Average Order Value, and Shipped Sales. Product and customer descriptions are removed from the fact table where they are not required for analysis. Data-quality checks are added to the refresh process to identify unmatched keys and unexpected row-count changes.

Outcome

Finance and regional management obtain consistent totals because the business definitions are centralised in the model. Users can analyse sales by order date or shipping date without confusing the two. Refresh performance improves because unnecessary columns and repeated transformations are removed.

Learning points

1. A trustworthy report begins with a defined grain.
2. A star schema reduces ambiguity and improves usability.
3. Business calculations should usually be implemented once as measures.
4. Date semantics must be visible to report users.
5. Data quality and model governance are ongoing processes, not one-time tasks.

9. Performance, Governance, and Ethical Considerations

9.1 Performance practices

Practical performance improvements include:

• removing unused columns and rows;
• using appropriate data types;
• reducing high-cardinality text fields where possible;
• preferring a simple star schema;
• avoiding unnecessary bidirectional relationships;
• using measures instead of excessive calculated columns;
• preserving query folding where practical; and
• testing refresh and visual response times with realistic data volumes.

Performance optimisation should not change the meaning of the data. A faster incorrect model is not an improvement.

9.2 Security and governance

Power BI models may contain personal, confidential, or commercially sensitive data. Governance should address:

• least-privilege access;
• row-level security for territories or business units;
• protection of personal information;
• approved data sources and ownership;
• refresh credentials and gateway security;
• documentation of measures and assumptions; and
• version control or controlled deployment practices.

Row-level security filters the rows visible to a user, but it must be tested carefully. Incorrect relationship paths can expose more data than intended or produce incomplete results.

9.3 Ethical interpretation

A dashboard can influence staffing, credit, pricing, and performance evaluation. Analysts should disclose data limitations, missing periods, definitions of metrics, and whether values are actual, estimated, or forecast. A report should not imply causation merely because two measures move together. Accessibility also matters: colour choices, labels, alternative descriptions, and clear language help prevent exclusion or misinterpretation.

10. Contemporary Developments

Modern Power BI solutions increasingly combine semantic models with governed data platforms, reusable certified datasets, deployment pipelines, and natural-language or AI-assisted features. AI can help users discover patterns or draft calculations, but generated results require human verification. The model’s definitions, relationships, security rules, and data lineage remain the foundation of trustworthy analysis.

A further development is the increased use of shared semantic models across departments. This can reduce conflicting definitions of revenue or customer count, but it also increases the importance of ownership, naming conventions, documentation, and change management.

Best-Practice Checklist

Before publishing a Power BI report, confirm that:

• every table has a documented grain;
• keys are unique on the one-side of one-to-many relationships;
• relationships have correct cardinality and direction;
• a dedicated date table is used for time analysis;
• measures represent shared business definitions;
• calculated columns are limited to stable row-level attributes;
• filters and inactive relationships are tested;
• totals are reconciled with an authoritative source;
• personal and confidential data are protected; and
• the report explains important assumptions and limitations.

Chapter Summary

Power BI combines data preparation, semantic modelling, calculation, and visual communication. Power Query is used to clean and reshape data before it enters the model. A semantic model defines entities, relationships, metadata, and calculations so that reports use consistent business meanings.

The star schema is a strong foundation for many analytical models. Fact tables record events at a defined grain, while dimension tables provide descriptive context. One-to-many relationships normally allow dimensions to filter facts. A dedicated date table supports consistent time analysis, and inactive relationships can support alternative date perspectives.

Calculated columns are evaluated row by row and stored in the model. Measures are evaluated at query time and respond to filter context. Visual calculations are useful for presentation-specific analysis but should not replace reusable measures for shared business definitions. Reliable Power BI development also requires attention to performance, security, privacy, accessibility, documentation, and ethical interpretation.

Key Terms

Semantic model: A business-oriented structure of tables, relationships, calculations, and metadata used for analysis.

Power Query: Power BI’s environment for connecting to, cleaning, and transforming data.

M language: The language used by Power Query to express transformation steps.

Fact table: A table containing business events or measurable observations.

Dimension table: A table containing descriptive attributes used to filter and group facts.

Grain: The precise meaning of one row in a table.

Star schema: A model with a central fact table connected to surrounding dimension tables.

Primary key: A column or combination of columns that uniquely identifies a row.

Foreign key: A column that refers to a key in another table.

Cardinality: The matching pattern between related tables, such as one-to-many.

Filter context: The set of filters affecting a DAX calculation.

Measure: A DAX expression evaluated dynamically at query time.

Calculated column: A stored, row-level calculation evaluated during refresh.

DAX: Data Analysis Expressions, the formula language used in Power BI models.

Query folding: The translation of Power Query operations into operations executed by the source system.

Date table: A dedicated table containing a continuous and structured calendar.

Inactive relationship: A relationship not used by default but available for selected calculations.

Row-level security: A technique that restricts the rows visible to particular users.

Bridge table: An intermediate table used to model certain many-to-many relationships.

Filter direction: The direction in which a relationship transfers filtering between tables.

Review Questions

Short Answer Questions

1. What is the purpose of Power Query in a Power BI solution?
2. Why must the grain of a fact table be documented?
3. What is the difference between a fact table and a dimension table?
4. Why is a dedicated date table useful?
5. How does a measure differ from a calculated column?

Long Answer Questions

1. Explain how a star schema supports reliable business analysis in Power BI.
2. Discuss the risks of using bidirectional relationships in a model.
3. Explain filter context and the role of CALCULATE in DAX.
4. Describe a systematic process for validating a newly imported sales dataset.
5. Compare Power Query transformations, calculated columns, measures, and visual calculations.

Analytical / Application Questions

1. A sales table contains one row per order line, while a promotion table contains one row per order. Explain why directly summing promotion values after a merge may produce an incorrect result, and propose a modelling solution.
2. A company needs reports based on OrderDate and ShipDate. Design the relationships and measures required to support both perspectives without confusing users.
3. A dashboard shows different revenue totals for finance and regional managers. Develop a diagnostic plan covering source quality, grain, relationships, measures, filters, and reconciliation controls.

Multiple Choice Questions with Answers

Question 1

Which Power BI component is primarily responsible for cleaning and reshaping data before it enters the model?

A. Power Query

B. Power View

C. The dashboard canvas

D. A report bookmark

Correct Answer: A. Power Query

Question 2

In a star schema, which table normally contains measurable business events?

A. Date dimension

B. Fact table

C. Product dimension

D. Bridge table

Correct Answer: B. Fact table

Question 3

What does the grain of a fact table describe?

A. The colour of its visuals

B. The number of dimensions in the report

C. What one row represents

D. The refresh frequency

Correct Answer: C. What one row represents

Question 4

Which calculation type is normally evaluated dynamically according to filter context?

A. Stored text column

B. Power Query parameter

C. Calculated column

D. Measure

Correct Answer: D. Measure

Question 5

Why is a dedicated date table commonly used?

A. To provide consistent calendar filtering and time analysis

B. To replace all fact tables

C. To prevent all data refreshes

D. To store only product descriptions

Correct Answer: A. To provide consistent calendar filtering and time analysis

Question 6

In a conventional star schema, what is the usual relationship between a product dimension and a sales fact table?

A. Many-to-many with no keys

B. One product to many sales rows

C. One sales row to one product dimension

D. Many products to one sales row only

Correct Answer: B. One product to many sales rows

Question 7

Which DAX function is commonly used to evaluate an expression in a modified filter context?

A. FORMAT

B. CALCULATE

C. COUNTROWS only

D. YEAR

Correct Answer: B. CALCULATE

Question 8

A calculated column is most appropriate when the result is needed as what?

A. A stable row-level attribute for grouping or filtering

B. A dynamic total that changes with slicers

C. A temporary page filter only

D. A relationship direction

Correct Answer: A. A stable row-level attribute for grouping or filtering

Question 9

What is query folding?

A. Compressing a report into a PDF

B. Translating suitable Power Query steps for execution by the source

C. Combining all dimensions into one table

D. Hiding a visual from users

Correct Answer: B. Translating suitable Power Query steps for execution by the source

Question 10

What is a major risk of directly joining an order-level discount to order-line data and summing it?

A. The date table becomes continuous

B. The discount may be repeated for every order line

C. The product key becomes unique

D. Measures stop responding to filters

Correct Answer: B. The discount may be repeated for every order line