Foundations of Data Modelling and DAX

Introduction

Business intelligence (BI) converts operational data into information that supports monitoring, explanation, prediction and decision-making. In Power BI, this conversion is not achieved merely by importing tables and drawing charts. It depends on a well-designed semantic model: a structured representation of business entities, relationships, calculations and definitions that enables users to interpret data consistently.

Data modelling provides the structural foundation of this model. It determines how sales, customers, products, dates and other business subjects are represented and related. DAX (Data Analysis Expressions) provides the calculation language used to create measures, calculated columns and calculated tables. A strong model combined with carefully designed DAX produces reports that are accurate, understandable, responsive and maintainable.

This chapter introduces the principal concepts required for further study in Power BI: BI architecture, semantic models, datasets, tables, columns, measures, dimensions, facts, granularity and the role of DAX.

Learning Objectives

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

• describe the main layers of a business intelligence architecture;
• distinguish a data source, dataset, semantic model and report;
• explain the purpose of tables, columns, relationships and measures;
• differentiate fact tables from dimension tables;
• define granularity and identify its importance in model design;
• explain the difference between calculated columns, calculated tables and measures;
• describe how DAX evaluates calculations through row context and filter context;
• identify common modelling errors and apply foundational design practices.

1. Business Intelligence Architecture

A BI environment normally contains several connected layers. Although the exact technology varies, the logical flow is commonly:

1. Source systems – transactional databases, enterprise resource planning systems, customer relationship management systems, spreadsheets, application programming interfaces and external data services.
2. Data preparation and integration – extraction, cleansing, standardisation, validation and transformation of source data.
3. Storage or analytical layer – a data warehouse, lakehouse, data lake or other governed analytical repository.
4. Semantic model – business-friendly tables, relationships, hierarchies, calculations, security rules and metadata.
5. Reports and dashboards – visual interfaces through which users explore and communicate results.
6. Decision and action – operational, tactical or strategic decisions informed by the analysis.

Power BI can participate in several of these layers. Power Query is primarily used for data connection and transformation. The Power BI model, formerly often called a dataset, provides the analytical or semantic layer. Reports and dashboards provide presentation and interaction.

Figure: A simplified business intelligence architecture.

The layers are conceptually distinct but often overlap in practice. For example, a small organisation may load spreadsheet data directly into Power BI, whereas a large organisation may use a governed lakehouse and a shared enterprise semantic model. The architectural principle remains the same: data must be transformed into a reliable analytical representation before users can draw dependable conclusions.

2. From Dataset to Semantic Model

Data, information and business meaning

Raw data consists of recorded observations, such as a transaction date, product identifier, quantity or sales amount. Information results when data is organised and interpreted. A statement such as “revenue increased by 8% in the West region during the second quarter” is informative because it combines measures, dimensions, time and a defined comparison.

Business meaning is not automatically contained in source data. A column called Amount may represent gross sales, net sales, tax-inclusive sales or a local-currency value. A semantic model documents and implements these meanings through names, data types, relationships, descriptions, measures and governance rules.

Dataset and semantic model

In Power BI terminology, a dataset has traditionally referred to the model containing imported or connected data used by reports. Microsoft increasingly uses the term semantic model because the object does more than store data: it expresses business concepts and analytical logic. In this chapter, semantic model is the preferred term.

A semantic model may contain:

• tables and columns;
• relationships between tables;
• calculated measures and other DAX expressions;
• hierarchies, display folders and formatting metadata;
• descriptions and synonyms for discoverability;
• row-level security rules;
• storage and refresh configuration.

A report is not the same as a semantic model. A report contains pages, visuals, filters and layout. Several reports can use one shared semantic model. This separation reduces duplicated logic and encourages consistent definitions of indicators such as revenue, margin and active customers.

Connectivity modes

Power BI models commonly use one or more of the following approaches:

• Import stores a compressed copy of data in the model and generally provides fast interactive analysis. Data must be refreshed.
• DirectQuery leaves data in the source system and generates queries when users interact with a report. It can support near-real-time scenarios but depends strongly on source performance and modelling restrictions.
• Live connection connects to an existing Analysis Services or Power BI semantic model, centralising modelling logic.
• Composite models combine storage modes or sources, but require careful attention to performance, relationships and consistency.

The appropriate choice depends on data volume, latency requirements, source capability, governance and user experience. No connectivity mode compensates for ambiguous business definitions or poor table design.

3. Tables, Columns and Relationships

A table is a structured collection of rows and columns representing a business subject or event. A column stores one attribute for each row, such as Customer Name, Product Category, Order Date or Quantity. Columns have data types, including text, whole number, decimal number, date/time, Boolean and currency-formatted numeric values.

Formatting does not change the underlying data type. For example, a decimal value formatted as currency remains numeric and can be aggregated; a text value containing a currency symbol generally cannot be reliably summed without transformation.

A relationship specifies how rows in one table correspond to rows in another. A common relationship is one-to-many: one row in a Product dimension corresponds to many rows in a Sales fact table. The one-side key must normally be unique, while the many-side contains repeated foreign-key values.

Important relationship 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: matching data types and appropriate values.

A model should generally favour a clear one-to-many design with filters flowing from dimensions to facts. Bidirectional filtering and many-to-many relationships can be valid, but they introduce ambiguity and should be used deliberately.

4. Facts, Dimensions and the Star Schema

Fact tables

A fact table records measurable business events or periodic snapshots. Examples include sales lines, inventory balances, web visits, payments and service calls. A fact table commonly contains:

• foreign keys to dimensions;
• numeric additive or semi-additive values;
• dates or times;
• an identifier for the event or transaction.

Dimension tables

A dimension table describes the context in which facts occur. Examples include Date, Customer, Product, Store, Employee and Geography. Dimensions contain descriptive attributes used to filter, group and label analyses.

Star schema

The preferred foundational design for many Power BI models is a star schema. A central fact table connects directly to surrounding dimension tables. The model resembles a star because dimensions radiate from the fact.

Figure: A simplified star schema for sales analysis.

A star schema usually improves usability because dimensions provide intuitive slicing and facts provide measures. It can also improve performance by reducing unnecessary joins and ambiguous filter paths. By contrast, a highly normalised snowflake design may reduce repetition in dimension data but often increases relationship complexity for report authors.

5. Granularity: The Most Important Modelling Decision

Granularity, or grain, is the level of detail represented by one row in a table. Examples include:

• one row per product sold on an invoice;
• one row per customer per day;
• one row per store per month;
• one row per warehouse inventory snapshot.

The grain must be stated explicitly before a fact table is built. If a Sales table has one row per order line, the quantity and sales amount belong to that order-line grain. A customer-level target table has a different grain and should not automatically be combined with the sales table by repeating target values across every order line.

Grain affects aggregation. If a value is recorded once per order, summing it after joining to order-line data may duplicate it. Likewise, an inventory balance cannot normally be summed across dates in the same way as sales transactions. Aggregation behaviour should therefore be classified:

A ratio should generally be calculated from its components rather than averaged across rows. For example, a business may define margin percentage as:

Here, Revenue and Cost must use the same filter context and currency basis, and Revenue must be non-zero. Averaging row-level margin percentages can produce a misleading result when transactions have different revenue values.

6. Measures, Columns and Calculated Tables

Measures

A measure is a model calculation evaluated when a visual, filter or query requests it. Its result changes according to the current filter context. A basic measure is:

Total Sales = SUM ( Sales[SalesAmount] )

If a report visual is grouped by year, the measure returns sales for each year. If the user selects a region, it returns sales for that region. Measures are therefore the normal choice for aggregations and business indicators.

Examples include:

Total Units = SUM ( Sales[Quantity] )

Total Cost = SUM ( Sales[CostAmount] )

Gross Margin = [Total Sales] - [Total Cost]

Margin % = DIVIDE ( [Gross Margin], [Total Sales] )

DIVIDE is generally safer than the / operator when the denominator may be zero or blank. The result of a ratio should also be formatted as a percentage and interpreted according to the organisation’s definition.

Calculated columns

A calculated column is computed for each row when data is loaded or refreshed. For example:

Line Margin = Sales[SalesAmount] - Sales[CostAmount]

Calculated columns are useful when the result must be stored and used for grouping, filtering, sorting or relationships. However, they increase model size and are not dynamically recalculated when a user changes a visual filter.

Calculated tables

A calculated table is created using DAX and stored in the model. It can support specialised modelling requirements, but it should not replace appropriate data preparation in Power Query or the source system. Tables that are static or refresh-dependent can often be prepared more transparently upstream.

Choosing the right object

7. The Role and Logic of DAX

DAX is a functional expression language designed for tabular analytical models. It is used to define calculations, but its most important feature is not arithmetic; it is evaluation under context.

Row context

Row context refers to the current row, especially in a calculated column or an iterator function. In a calculated column, Sales[Quantity] * Sales[UnitPrice] is evaluated for each row independently.

Filter context

Filter context is the set of filters affecting a calculation. It may come from visual axes, slicers, page filters, report filters, relationships or explicit DAX expressions. A measure such as Total Sales is evaluated under the current filter context.

Context transition

Some DAX operations, particularly CALCULATE, transform an existing row context into filter context. This mechanism is called context transition. It is powerful but can be difficult for beginners; it is best learned after the basic distinction between row and filter context is clear.

CALCULATE

CALCULATE evaluates an expression after modifying its filter context. For example:

Sales LY =
CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, YEAR ) )

This calculation assumes that the Date table is properly related to Sales, contains an appropriate continuous date column, and is configured for time-based analysis. The expression shifts the date context one year backward. Comparisons should also account for incomplete periods and unusual calendar structures.

Iterators

Iterator functions, commonly recognised by an X suffix, evaluate an expression row by row and then aggregate the results. For example:

Total Extended Cost =
SUMX ( Sales, Sales[Quantity] * Sales[UnitCost] )

This is appropriate when the required amount is not already stored as a column. It should not be used automatically: if ExtendedCost is already a reliable column, a simple SUM may be clearer and more efficient.

8. Worked Example: A Small Retail Model

Consider the following illustrative tables:

Sales has one row per order line and contains OrderDate, ProductKey, CustomerKey, Quantity, UnitPrice and UnitCost. Product contains ProductKey, ProductName and Category. Customer contains CustomerKey, CustomerName and Region. Date contains one row for each calendar date, with Year, Month and Month Number.

Relationships are:

• Date[Date] → Sales[OrderDate], one-to-many;
• Product[ProductKey] → Sales[ProductKey], one-to-many;
• Customer[CustomerKey] → Sales[CustomerKey], one-to-many.

Suppose three order lines are recorded:

Revenue is calculated per line as Quantity × Unit price. Therefore total revenue is 100 + 80 + 100 = 280. Total cost is (2 × 30) + (1 × 45) + (4 × 15) = 165. Gross margin is 280 − 165 = 115, and margin percentage is 115 ÷ 280 × 100 ≈ 41.1%.

The corresponding measures could be:

Total Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

Total Cost = SUMX ( Sales, Sales[Quantity] * Sales[UnitCost] )

Gross Margin = [Total Revenue] - [Total Cost]

Margin % = DIVIDE ( [Gross Margin], [Total Revenue] )

The measure Margin % returns approximately 41.1% for the full sample. If a visual is filtered to only the first and third lines, revenue becomes 200, cost becomes 120, and margin percentage becomes 40%. This illustrates why a measure is preferable to averaging row-level percentages: it recalculates from the filtered numerator and denominator.

9. Illustrative Case: Regional Retail Performance

Background

A fictional retailer, Northstar Home, sells household products through stores and an online channel. Managers currently receive spreadsheets from regional teams. Each spreadsheet uses different definitions of sales, includes duplicated product names and reports monthly totals at inconsistent levels of detail.

Problem

The executive team cannot answer basic questions consistently: Which region generated the highest net sales? Are margins declining because of price reductions or rising costs? Are online and store results being compared at the same grain?

Analysis

An initial assessment identifies three issues. First, the spreadsheets mix order-level and monthly summary data. Second, product categories are entered manually, producing spelling variations. Third, managers average percentages from individual stores rather than calculating a ratio from regional totals.

Approach and solution

The BI team establishes one row per sales order line as the grain of the Sales fact table. It creates separate Date, Product, Customer and Region dimensions. Product categories are standardised during data preparation. Revenue, cost, gross margin and margin percentage are implemented as measures. A shared semantic model is published for executive and regional reports.

The model also documents that revenue excludes sales tax, cost represents product cost, and margin percentage is calculated as total gross margin divided by total revenue. Security rules restrict regional managers to their authorised regions.

Outcome

Managers can compare regions using a common definition. A regional slicer filters the same Sales fact table through the Region dimension, while measures recalculate appropriately. The executive report identifies a decline in margin caused primarily by higher product costs rather than reduced prices. Because the model separates dimensions from facts, new products and customers can be added without redesigning the report pages.

Learning points

The case demonstrates that visual design cannot repair inconsistent grain or unclear definitions. It also shows the value of centralised measures, conformed dimensions, documented assumptions and controlled access. The technical model becomes useful only when it reflects the organisation’s business meaning.

10. Common Modelling Errors and Best Practices

Frequent errors

• Combining facts with different grains in one table without a clear purpose;
• storing dates as text rather than using a proper Date dimension;
• using measures where a stable grouping column is required;
• creating excessive calculated columns when transformations could occur upstream;
• relying on bidirectional relationships to resolve an unclear model;
• using many-to-many relationships without understanding duplicate propagation;
• averaging ratios that should be recalculated from additive components;
• allowing multiple definitions of the same KPI across reports;
• hiding data-quality problems through DAX rather than correcting the source or preparation layer.

Foundational best practices

1. Define the grain of every fact table in one sentence.
2. Use meaningful, consistent names and hide technical keys from report users.
3. Prefer a star schema with dimensions filtering facts.
4. Create explicit measures for important business calculations.
5. Maintain a dedicated, contiguous Date table for time analysis.
6. Use correct data types and appropriate formats.
7. Document definitions, assumptions, refresh timing and ownership.
8. Validate totals against a trusted operational or financial source.
9. Test calculations under different filters, including empty and zero-value cases.
10. Apply least-privilege access and protect sensitive personal or commercial data.

11. Governance, Ethics and Contemporary Practice

A semantic model can expose commercially sensitive information, personal data and inferred characteristics. Governance therefore includes access control, lineage, retention, classification, auditing and responsible use. Row-level security should be tested, not assumed. A report showing customer profitability may require stronger controls than a report showing aggregated product sales.

AI-assisted features can help users generate natural-language questions, summaries or draft calculations. However, AI does not establish whether a measure is conceptually valid. Users must verify the source, grain, filter behaviour and business definition of any generated expression. Automated narratives can also create misleading impressions when data is incomplete or biased.

Contemporary BI practice increasingly emphasises reusable enterprise semantic models, certified datasets, data products, real-time or near-real-time ingestion and metric governance. These developments increase the value of foundational modelling knowledge: new interfaces still depend on reliable relationships, well-defined measures and trustworthy data.

Chapter Summary

Business intelligence architecture moves data from source systems through preparation and storage into a semantic model, reports and decisions. In Power BI, the semantic model expresses business meaning through tables, columns, relationships, calculations, metadata and security.

Fact tables record events or snapshots, while dimension tables provide descriptive context. A star schema usually offers a clear and effective foundation for interactive analysis. Granularity identifies what one row represents and must be specified before measures or relationships are designed. Additive, semi-additive and non-additive values require different aggregation strategies.

Measures are dynamic calculations evaluated in filter context and are normally the correct choice for KPIs. Calculated columns operate row by row at refresh time, while calculated tables create stored tables in the model. DAX supplies the language for these objects and relies on concepts including row context, filter context, context transition, CALCULATE and iterator functions.

Reliable Power BI solutions require more than correct syntax. They require consistent definitions, suitable grain, validated relationships, documented assumptions, responsible security and testing under realistic filters.

Key Terms

Semantic model: A governed analytical representation of business data, relationships, calculations and metadata.

Dataset: A traditional Power BI term for a model used by reports; increasingly replaced by semantic model.

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

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

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

Granularity: The level of detail represented by one row in a table.

Measure: A dynamic DAX calculation evaluated in the current filter context.

Calculated column: A column computed for each row during refresh.

Calculated table: A table generated by an expression and stored in the model.

Relationship: A defined correspondence between columns in two tables.

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

Filter context: The collection of filters affecting a DAX expression.

Row context: The current row being evaluated by a calculated column or iterator.

Context transition: The conversion of row context into filter context, commonly associated with CALCULATE.

DAX: Data Analysis Expressions, the calculation language for tabular models.

Additive measure: A value that can be summed across the relevant dimensions.

Semi-additive measure: A value that can be summed across some dimensions but not all, often time.

Non-additive measure: A value, such as a ratio, that should not be summed directly.

Dimension key: A value that identifies a dimension row and is referenced by facts.

Row-level security: A control that restricts the rows visible to a user.

Review Questions

Short Answer Questions

1. What is the purpose of a semantic model in Power BI?
2. Define granularity and explain why it should be stated before creating a fact table.
3. What is the difference between a fact table and a dimension table?
4. Distinguish a measure from a calculated column.
5. What is filter context in DAX?

Long Answer Questions

1. Explain the major layers of a business intelligence architecture and describe how Power BI can participate in each layer.
2. Describe the structure and advantages of a star schema for retail sales analysis.
3. Explain additive, semi-additive and non-additive values, giving one example of each.
4. Compare Import, DirectQuery and live connection approaches in terms of storage, performance and governance.
5. Explain row context, filter context and context transition, and discuss why these concepts matter when writing DAX.

Analytical / Application Questions

1. A sales table contains one row per order line, while a target table contains one row per region per month. Explain why directly joining the tables may produce incorrect totals and propose a suitable model design.
2. A report calculates regional margin by averaging the margin percentages of individual stores. Critique this approach and specify a more reliable calculation.
3. Design the main tables, relationships and two measures for a service organisation that wants to analyse tickets by date, customer, support agent, priority and resolution status.

Multiple Choice Questions with Answers

Question 1

Which component primarily represents business meaning, relationships and reusable calculations in Power BI?

A. The report canvas

B. The semantic model

C. The source spreadsheet

D. The visual theme

Correct Answer: B. The semantic model

Question 2

In a typical star schema, which table records individual sales events?

A. Date dimension

B. Product dimension

C. Sales fact table

D. Customer dimension

Correct Answer: C. Sales fact table

Question 3

What does granularity specify?

A. The colour scheme of a report

B. The level of detail represented by one row

C. The number of report pages

D. The refresh frequency

Correct Answer: B. The level of detail represented by one row

Question 4

Which object normally recalculates when a report user changes a slicer?

A. A measure

B. A stored calculated column

C. A source-system key

D. A table name

Correct Answer: A. A measure

Question 5

Which DAX concept describes the filters supplied by visuals, slicers and relationships?

A. Row context

B. Filter context

C. Data type

D. Cardinality

Correct Answer: B. Filter context

Question 6

A fact table contains three lines with revenues of 100, 80 and 100. What is total revenue?

A. 93.3

B. 180

C. 280

D. 300

Correct Answer: C. 280

Question 7

If revenue is 280 and cost is 165, approximately what is the margin percentage calculated as (Revenue − Cost) ÷ Revenue × 100?

A. 29.5%

B. 41.1%

C. 58.9%

D. 115%

Correct Answer: B. 41.1%

Question 8

Which relationship is most typical between a Product dimension and a Sales fact table?

A. Many-to-many with no keys

B. One-to-many from Product to Sales

C. One-to-one from Sales to Product for every order line

D. No relationship is required

Correct Answer: B. One-to-many from Product to Sales

Question 9

Which measure is most appropriate for calculating line-level quantity multiplied by unit price before summing?

A. SUMX over the Sales table

B. COUNTROWS over the Date table

C. AVERAGE over the Product table

D. DISTINCTCOUNT over Region

Correct Answer: A. SUMX over the Sales table

Question 10

Why is averaging store-level margin percentages often problematic?

A. Percentages cannot be displayed in Power BI

B. Stores may have different revenue weights

C. Dimensions cannot filter facts

D. Measures cannot use subtraction

Correct Answer: B. Stores may have different revenue weights