14  Data Models and DAX Measures

Learning Objectives

After completing this chapter, you will be able to:

1. Design a star schema from the Charles River tables, naming its fact and dimension tables and its grain, and flatten a header-line pair into one fact table.
2. Build and mark a date table, set the cardinality and cross filter direction of relationships, and use an inactive relationship for a second date.
3. Write explicit measures with SUM, DIVIDE, SUMX, RELATED, and variables, and choose between a measure, a DAX calculated column, and a Power Query column.
4. Explain filter context, row context, and context transition, and change the filter context with CALCULATE.
5. Compare periods with time intelligence functions.
6. Model the general ledger so that year-end closing entries and debit and credit signs are handled correctly, and test measures in DAX query view against control totals.

Chapter slides View slides Download PowerPoint

Opening Scenario

The report you built in Chapter 13 has been in use for a few weeks, and the questions it raises have outgrown it. The sales manager wants to know what the autumn discounts cost in margin, not only in dollars. The marketing manager asks how each month compares with the same month last year. A product manager noticed that the invoice count on a draft page did not change when Furniture was selected and asked which number was right. Each of these is a calculation the report cannot yet make: a ratio of totals, a comparison across periods, and a count that depends on how the tables are joined.

The controller has a request of her own. “If this report is where the managers look,” she says, “the income statement should be there too, monthly, from the ledger, and it has to agree with the statements we closed.” That means a second set of tables, the general ledger and its chart of accounts, in the same report, sharing the same calendar as the sales data. This chapter teaches you to design that model and to write its calculations in DAX, the formula language of Power BI. You will reshape the sales tables into a star schema, add a date table and the ledger, write measures for margin, discount rate, and the income statement, compare periods with time intelligence, and test each measure against a total you already trust.

14.1 From Tables to a Model

A semantic model, as Chapter 13 explained, is the set of tables, relationships, and calculations that a report reads. The way those tables are arranged decides how easy the model is to use and how reliably its numbers come out. The arrangement that Power BI is designed for, and that most business intelligence models use, is the star schema, the design that Kimball and Ross (2013) describe for data warehouses. A star schema separates two kinds of tables. A fact table records events or transactions, one row for each event, with the keys of the things involved and the numbers to be added up: an invoice line with its item, customer, date, quantity, and amount. A dimension table describes one kind of thing, one row for each, with the attributes a reader filters and groups by: an item with its group and product type, a customer with its segment and region, a date with its year and month (Microsoft, 2024d). Each dimension relates to the fact table on its key, so the diagram of the model looks like a star, with the fact table at the center.

Every fact table has a grain, the event that one of its rows represents. Stating the grain is the first decision in a model, because it decides which questions the table can answer. The grain of SalesInvoiceLine is one line of one invoice, which can be totaled by item, customer, or day, but not split by shipment if a line shipped in parts. Table 14.1 lists the fact tables of the Charles River models in this Part, with their grain, their dimensions, and the chapter that adds them.

Table 14.1: Fact tables of the Charles River reports, with their grain and dimensions

Fact table	Grain: one row for each	Dimensions	Chapter
SalesInvoiceLine	line of an invoice	Item, Customer, Date	13, 14
GLEntry	posting to an account	Account, CostCenter, Date	14
BudgetLine	month, account, cost center, and item	Account, CostCenter, Date	15
LaborTimeEntry	time entry of an employee	Employee, WorkCenter, Date	15
ProductionCompletionLine	item line of a completion	Item, Date	15
JournalEntry, PurchaseOrder, PayrollRegister	document tested for exceptions	Employee, Date	16
Exceptions	exception of one test on one document	Tests, Employee, Date	16

The ER diagrams of Chapter 3 drew the same tables in a different arrangement, because a database is designed to record transactions without repeating any fact. A sale is split across a header and its lines, and the customer lives on the header, not on each line. Read through Power BI’s eyes, that header-line pair is a snowflake: a dimension, Customer, reaches the fact table only through another table, SalesInvoice. The model of Chapter 13 worked because a filter can travel down a chain of one-to-many relationships, but it has two weaknesses. The header table splits the invoice’s attributes from its lines, so a count taken from the header ignores a filter on the lines, as you saw in Tutorial 13.2. And a second date, such as the date a line shipped, has no clean place to go. The usual remedy is to flatten the pair: to merge the header’s attributes into the lines, as you did with lookups and merges in Chapter 5, so that every dimension relates to the fact table directly. Kimball and Ross (2013) recommend this for every header-line pair: the header’s dimension keys go on each line, and so does an attribute such as the invoice number, which becomes a degenerate dimension, a column of the fact table that filters and groups without a dimension table of its own. Microsoft’s guidance makes the same choices for Power BI: it generally prefers one model table to a chain of normalized tables, and keeps an attribute such as an order number in the fact table (Microsoft, 2024d). O’Leary (1999) made a related point about accounting data in particular: a warehouse built from accounting records needs time as a dimension of its own, so that events can be rolled up from day to month to year.

Figure 14.1 shows the sales tables of Chapter 13 in both arrangements.

Figure 14.1: The Sales Tables as a Snowflake and as a Star.

NoteConnecting the Dots

Flattening the header into its lines is the merge of Tutorial 5.3, done for a different reason. In Chapter 5, the merge brought the customer’s segment into the InvoiceLines Table so that a formula could use it. Here it brings the invoice’s date and customer key into the fact table so that the date and customer dimensions can relate to the lines directly. Chapter 10 warned that joining a header to its lines repeats the header’s amounts on every line. The same warning applies here: bring only keys and descriptive attributes from the header into the lines, never the header’s totals.

14.2 Relationships

Chapter 13 introduced relationships with their defaults. Two settings decide how a relationship behaves. Its cardinality says how many rows on each side can match: many to one, one to one, or many to many, in Power BI’s terms, which correspond to the cardinalities of Chapter 3. A star schema is built almost entirely from many-to-one relationships, from the fact table to each dimension. A one-to-one relationship usually signals two tables that are really one. ProductionCompletion and its lines, for example, have exactly one line per completion in the Charles River data, and Power BI proposes a one-to-one relationship between them. A many-to-many relationship signals a missing table in between, such as the junction tables of Chapter 3. Both deserve a second look before you accept them.

The second setting is the cross filter direction. With Single, the default for a many-to-one relationship, a filter travels from the one side to the many side: from Item to the invoice lines, never from the lines back to Item. With Both, it travels in both directions. Both can seem like a convenient fix, for example to make the invoice count of Tutorial 13.2 respond to an Item selection. Microsoft’s guidance is to keep relationships single-directional whenever possible, because filters that travel both ways can slow a model’s queries and confuse its readers (Microsoft, 2024b). In a model with two fact tables, they can also open paths between tables that the author did not intend, as the Watch out below explains. The better remedy, which this chapter applies, is to put the attribute in the fact table and count there.

WarningWatch out

Setting a relationship to Both makes a number change when you click, which can look like a fix. Check what the number now means before you accept it. In a model with two fact tables sharing a dimension, a two-way filter can carry a selection from one fact table, through the dimension, into the other, so that selecting a product filters the ledger, or the reverse. Keep every relationship Single unless you can state in one sentence why a filter must flow the other way.

Between two tables, only one path of relationships can be active at a time. A model may need a second relationship anyway: an invoice line has an invoice date and a shipment date, and both may be compared with the same calendar. Power BI allows the second relationship but makes it inactive, drawn as a dashed line in Model view. An inactive relationship filters nothing until a measure switches it on with the USERELATIONSHIP function. The date table then plays two roles, invoice date and ship date, and the measure decides which (Microsoft, 2024a). Finally, a reminder from Chapter 13: when a key on the many side finds no match, its rows are shown against a blank member, (Blank), of the dimension. In the ledger, about one posting in seven has no cost center, almost all of them postings to asset and liability accounts, and they appear under (Blank) in any visual grouped by cost center.

14.3 The Date Table

Chapter 13’s report took its years and months from columns added to the invoice table. That works for one table, but the ledger has dates of its own, and a model with two fact tables needs one calendar that both share. A date table is a dimension with one row for every day in the period the model covers, and columns for the year, quarter, month, and any other way the business groups days. It is also what DAX’s time intelligence functions need in order to find the same period last year or the year to date (Microsoft, 2025c).

For the classic time intelligence functions that this book uses, Microsoft’s guidance sets four requirements for the date table’s column of dates: unique values, no blanks, no missing days, and full years (Microsoft, 2024c). The Charles River ledger runs from January 1, 2024, into February 2027, so the date table runs from January 1, 2024, to December 31, 2027. Charles River’s fiscal year is the calendar year, so the year of a date is its fiscal year. The table can be built in Power Query or in DAX. This chapter uses DAX, with the CALENDAR function, which returns one row for each day between two dates, wrapped in ADDCOLUMNS, which adds the other columns. Once the table exists, you mark it as the model’s date table, which tells Power BI which column holds the dates and lets the classic time intelligence functions rely on it.

One column needs care. A month name such as Jan sorts alphabetically, so April would come first and January in the middle. A column’s sort order can be set to follow another column, here the month number, with Sort by column. The YearMonth column, which reads like 2026-01, sorts correctly as text, as Month did in Chapter 13.

WarningWatch out

Some names are reserved words in DAX, and a table with such a name must be written in single quotes in every formula. Date is one of them, so the date table is always ‘Date’, and so is Item, which must be written ‘Item’. A formula with an unquoted reserved name fails with a syntax error, and every visual that uses the broken measure, or a measure built on it, shows an error until the name is quoted. Choosing a name from the suggestion list as you type adds the quotes for you.

14.4 Guided Tutorial 14.1: Building the Sales Star and the Date Table

Context and objective. In this tutorial, you will turn the model of Chapter 13 into a star schema. You will merge the invoice header’s date, number, and customer into the invoice lines, add a date table, connect the dimensions directly to the lines, and point the report’s visuals at the date table. Then you will stop loading the invoice header and confirm on the Validation page that no total has changed. Restructuring a model is a common task, and the order of the steps matters: each visual must stop using a table before the table leaves the model.

Prerequisites. You need Charles River Reports.pbix from the tutorials of Chapter 13, with its Sales and Discounts, Validation, and Review pages. If you start here, download Charles River Reports as it stands at the end of Chapter 13 from Companion Files: a Power BI project that you point at your copy of CharlesRiver.xlsx, refresh, and save as Charles River Reports.pbix, as that page explains.

Step-by-step instructions.

Step 1. Merge the invoice header into the lines. The lines become the fact table, so they need the invoice’s date, number, and customer.

1. Choose Home > Transform data to open the Power Query Editor.
2. Select the SalesInvoiceLine query, and choose Home > Merge Queries.
3. In the Merge dialog, click SalesInvoiceID in the upper table, choose SalesInvoice in the lower list, click its SalesInvoiceID, leave Join Kind at Left Outer, and click OK.
4. Click the expand button on the new column, select only InvoiceNumber, InvoiceDate, and CustomerID, clear Use original column name as prefix, and click OK.
5. Choose Home > Close & Apply.

The invoice lines now carry the three columns, and the match message of the merge reported that every line found its invoice, as in Tutorial 5.3. The SalesInvoice query still loads, for now, because the report’s visuals still use its year and period columns.

Step 2. Create the date table. A DAX table covers every day of the ledger’s years.

1. In Report view, choose Modeling > New table.
2. Replace the text in the formula bar with the formula below, and press Enter.

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2027, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "YearQuarter", YEAR ( [Date] ) & "-Q" & QUARTER ( [Date] ),
    "YearMonth", FORMAT ( [Date], "yyyy-mm" ),
    "MonthNumber", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmm" )
)

The Data pane now lists a table named Date with seven columns. CALENDAR supplies the Date column, and each pair in ADDCOLUMNS adds one column: a name in quotes and the formula that fills it.

Step 3. Sort the month names and mark the date table.

1. Select the Date column of the Date table in the Data pane, and on the Column tools tab set Data type to Date, so that the dates show without a time.
2. Select the MonthName column, and on the Column tools tab choose Sort by column > MonthNumber.
3. Select the Year column, and on the Column tools tab set Summarization to Don’t summarize. Do the same for MonthNumber.
4. Right-click the Date table in the Data pane, and choose Mark as date table.
5. Choose Date as the date column, and confirm.
6. Select Table view, select the Date table, and read the number of rows in the status bar.

The table has one row for each day from January 1, 2024, to December 31, 2027, four full years, as Figure 14.2 shows. Year and MonthNumber are whole numbers, which Power BI would otherwise sum, so they are set to act as categories, as FiscalYear was in Chapter 13. Marking the table lets the time intelligence functions of Tutorial 14.3 rely on it.

Figure 14.2: The Date Table in Table View.

Step 4. Replace the relationships. Two relationships of the header-line chain give way to direct ones.

1. Select Model view.
2. Select the line between SalesInvoice and SalesInvoiceLine, press Delete, and confirm.
3. Delete the line between Customer and SalesInvoice in the same way.
4. Drag InvoiceDate from SalesInvoiceLine onto Date in the Date table.
5. Drag CustomerID from SalesInvoiceLine onto CustomerID in Customer.

If a relationship window opens for a drag, check that it reads many to one, with a single cross filter direction, and save it. If none opens, read the new line in Model view instead. It should show 1 at the dimension, an asterisk at the lines, and one arrow pointing toward the lines. The lines now sit at the center of three dimensions: Item, Customer, and Date. SalesInvoice is left unrelated, and its year slicer has stopped filtering anything, which the next step corrects.

Step 5. Point the visuals at the date table. Table 14.2 lists each field to replace. To replace a field, drag its replacement from the Data pane onto the field in the visual’s well, or onto its filter in the Filters pane.

1. On the Sales and Discounts page, replace the fields of the year slicer, the line chart’s axis, and the matrix’s columns.
2. On the Validation page, replace FiscalYear in the table of totals.
3. On the Review page, replace the fields and the visual filters of the four charts.
4. Set the year slicer on Sales and Discounts back to 2026, and the visual filters of the Review page’s three monthly charts back to 2026. The chart of revenue by fiscal year has no visual filter and keeps all three years.

Table 14.2: Fields replaced in Tutorial 14.1

Field from SalesInvoice	Replace with, from Date
FiscalYear	Year
Period	YearQuarter
Month	YearMonth

Each visual now takes its periods from the date table. The values do not change, because each invoice date has the same year, quarter, and month in both tables.

Step 6. Stop loading SalesInvoice.

1. Choose Home > Transform data.
2. Right-click the SalesInvoice query, and clear Enable load.
3. Choose Home > Close & Apply.

SalesInvoice disappears from the Data pane but stays in the Power Query Editor, where SalesInvoiceLine still uses it for the merge. If any visual now shows an error instead of a value, it still uses a SalesInvoice field: replace the field as in Step 5.

Step 7. Hide the keys. Keys connect the tables, but a reader has no use for them.

1. In Model view, select SalesInvoiceLineID, SalesInvoiceID, ItemID, and CustomerID in SalesInvoiceLine, together with ItemID in Item and CustomerID in Customer, holding Ctrl to select several.
2. In the Properties pane, turn on Is hidden and set Summarize by to None.

The keys no longer appear in the Data pane of Report view, so no one can sum them by mistake.

Step 8. Reconcile the restructured model.

1. Open the Validation page.
2. Compare each row of the table of totals with the values you recorded in Tutorial 13.1.
3. Save the report.

Every year’s line count, line total, list amount, and discounts should be unchanged. With the merge of Step 1, which matched every line to its invoice, that is good evidence that the restructuring lost no invoice line and moved none to another year, although equal totals alone could hide changes that offset, as Tutorial 4.1, Step 10 showed. Figure 14.1 compares the model before and after.

Checkpoint. Before you write measures on the new model, confirm the following:

• The model view shows SalesInvoiceLine related to Item, Customer, and Date, each many to one with a single direction, and SalesInvoice is no longer a table of the model.
• The Date table is marked as the date table and has one row per day for four full years, with MonthName sorted by MonthNumber.
• No visual shows an error, and the Validation page’s totals equal those of Tutorial 13.1.

A model is restructured safely only when the totals it reports are the same before and after. With the star in place, every dimension filters the lines directly, which the measures of Tutorial 14.2 rely on.

14.5 Measures and Calculated Columns

Chapter 13’s visuals summarized columns with implicit measures. An explicit measure is a named calculation written in DAX, short for Data Analysis Expressions, the formula language of Power BI. A measure is defined once in the model and can be used in any visual, where it is calculated for every cell from the rows that the cell’s filters keep. Revenue, defined as SUM ( SalesInvoiceLine[LineTotal] ), is one sum in a card, twelve sums in a chart by month, and one sum for each item group and quarter in a matrix. A measure is the Power BI counterpart of Chapter 6’s calculated field, which also works on totals. Explicit measures are preferred over implicit ones because they have names a reader understands, carry their own format, and can be used inside other measures, so the definition of revenue lives in one place. Power BI can even be set to discourage implicit measures for a model, which hides the summation sign from numeric columns.

DAX can also add a calculated column to a table. A calculated column is evaluated once for each row when the model is refreshed and is stored like any other column, so it can be used to filter, group, or sort. A measure is evaluated when a visual asks for it, and its result is never stored. The rule of thumb follows: if the value belongs to each row and you want to slice by it, it is a column; if it is a total, a ratio of totals, or anything that should change with the reader’s selection, it is a measure. A column that can be computed from the row itself, such as the list amount of an invoice line, is usually better added in Power Query, where it is part of the documented preparation steps, as in Chapter 13. A DAX column is the tool for values that need the model, such as a value from a related table.

A model with many measures needs order. A common practice is to keep the measures in a table of their own, created with Enter data, so that they appear together at the top of the Data pane. This book names it Key Measures, because Power BI Desktop does not accept a table named Measures. Each measure should have a format, such as two decimals with a thousands separator or a percentage, and a description, which Power BI shows to anyone who points at the measure. Display folders group related measures within a table.

14.6 Evaluation Context

Every DAX formula is evaluated in a context, and understanding the context is the key to reading DAX. The filter context is the set of filters in effect where a measure is evaluated. In a matrix of revenue by item group and quarter, the cell for Furniture in 2026-Q4 is evaluated with three filters: the item group is Furniture, the quarter is 2026-Q4, and anything selected in the page’s slicers. Power BI applies those filters to the dimension tables, the relationships carry them to the fact table, and SUM adds up the invoice lines that remain. Figure 14.3 traces one such cell.

Figure 14.3: The Filter Context of One Matrix Cell.

The row context is different: it is the current row of a table, which exists while DAX works through a table one row at a time. A calculated column has a row context, because it is evaluated for each row. So do the iterator functions, such as SUMX, which evaluates an expression for each row of a table and adds up the results. Many iterators have names ending in X, but not all: FILTER and ADDCOLUMNS, which this chapter also uses, iterate too. Standard cost is an iterator’s job. The invoice lines have a quantity but no cost, and the cost per unit lives on the Item table. SUMX ( SalesInvoiceLine, SalesInvoiceLine[Quantity] * RELATED ( 'Item'[StandardCost] ) ) multiplies each line’s quantity by its item’s standard cost and adds the products. RELATED, which needs a row context and a many-to-one relationship to follow, goes from the current line to the one row of Item it belongs to, the DAX counterpart of the lookups of Chapter 5. Multiplying the totals instead, the sum of quantities by the sum of costs, would give a meaningless number, for the reason Chapter 6 gave for calculated fields.

The two contexts meet in context transition. A row context does not filter anything by itself. In a calculated column on the Customer table, SUM ( SalesInvoiceLine[LineTotal] ) returns the revenue of every invoice line for every customer, because nothing filters the lines. Wrapped in CALCULATE, the same expression returns each customer’s own revenue: CALCULATE turns the current row into a filter on the Customer table, and the relationship carries it to the lines. This is context transition, and it happens automatically whenever a measure is referred to inside a row context, because every measure behaves as if it were wrapped in CALCULATE. Figure 14.4 shows the two columns side by side.

Figure 14.4: Row Context With and Without Context Transition.

WarningWatch out

Context transition filters on every column of the current row. On a dimension with a unique key, that picks out exactly one row, as intended. On a table with no unique key, such as a fact table whose rows can repeat, it picks out every identical row at once, and a formula that seems to calculate one row’s value adds up all its duplicates. Use measures inside iterators over dimension tables, and over fact tables only when each row is unique.

14.7 CALCULATE

CALCULATE evaluates an expression in a modified filter context, and it is the function that most DAX measures are built on. Its first argument is the expression, and each further argument is a filter. CALCULATE ( [Revenue], 'Item'[ItemGroup] = "Furniture" ) returns Furniture’s revenue. A filter argument replaces any existing filter on the same column, so the measure returns Furniture’s revenue even in a visual or slicer filtered to Lighting. That is often what you want, for a column that should show one group’s figure whatever the selection, but not always. The KEEPFILTERS function changes the behavior so that the new filter is combined with the existing one instead of replacing it (Microsoft, 2026).

Removing filters is the other common use. A share of the total divides a value by the same value without some of its filters. DIVIDE ( [Revenue], CALCULATE ( [Revenue], REMOVEFILTERS ( 'Item' ) ) ) divides each item group’s revenue by the revenue of all items, while keeping the filters on year, customer, and anything else. The choice of what to remove is the substance of the measure. Removing the filters from the whole invoice line table instead, with ALL ( SalesInvoiceLine ), would remove the year as well, because inside CALCULATE, ALL on a fact table also removes the filters on the dimension tables it relates to, Date among them. Each item group’s share of fiscal 2026 would then be divided by three years of revenue, and Furniture’s share would read as about a fifth instead of more than half.

Figure 14.5 compares the two measures in a table of item groups filtered to fiscal 2026.

Figure 14.5: A Filter That Replaces and a Filter That Is Removed.

Two habits make measures reliable. The first is DIVIDE, which returns a blank instead of an error when the denominator is zero, as it is for a group with no sales in a period. The second is to filter on columns rather than on whole tables. In an Import model such as this one, a filter argument on one column, such as GLEntry[Debit] > 10000, is the efficient form, while FILTER over a whole fact table does the same job with more work. Microsoft’s guidance is to use the column form whenever it can express the condition, and FILTER when the condition needs it, for example to test a measure (Microsoft, 2021a). Variables, written with VAR and RETURN, make longer measures readable: a variable holds an intermediate result under a name, is evaluated once, and can be used several times (Microsoft, 2021b). Table 14.3 summarizes the functions this chapter uses.

Table 14.3: DAX functions used in this chapter

Function	What it does	Example in this chapter
SUM	Adds up a column over the rows in the filter context	Revenue
DISTINCTCOUNT	Counts the different values of a column	Invoices
DIVIDE	Divides, returning a blank when the denominator is zero	Margin %
SUMX	Evaluates an expression row by row and adds the results	Standard Cost
RELATED	Returns a value from the one side of a relationship	Standard Cost
CALCULATE	Evaluates an expression in a modified filter context	Furniture Revenue
REMOVEFILTERS	Removes filters from a table or column inside CALCULATE	Share of Revenue
USERELATIONSHIP	Activates an inactive relationship inside CALCULATE	Revenue by ship date
VAR, RETURN	Name an intermediate result and use it	Margin %

14.8 Testing Measures in DAX Query View

A measure that returns a number is not yet a measure that returns the right number. In a workbook, you checked a formula against a control total. In SQL, you compared a query’s result with a total from another source. In Power BI, DAX query view is the place for the same check. It runs DAX queries against the model and shows the result in a grid, without building a visual. A query begins with EVALUATE, followed by an expression that returns a table, such as SUMMARIZECOLUMNS, which groups by columns and calculates measures for each group, much like a SQL query with GROUP BY:

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Year],
    "Lines", COUNTROWS ( SalesInvoiceLine ),
    "Revenue", ROUND ( [Revenue], 2 )
)
ORDER BY 'Date'[Year]

The result has one row for each year, with the number of invoice lines and the revenue. Its fiscal 2026 row must equal the control total of Chapter 6. The results grid shows values without the formats set for the measures, so a test rounds amounts to the cent with ROUND, as here, and they read as they do in the earlier chapters. A query can also define a measure for its own use, with DEFINE MEASURE, and test it before it is added to the model. Right-clicking a table or measure in the Data pane of DAX query view offers quick queries, such as Show top 100 rows and Define and evaluate, which write a query for you. In Power BI Desktop, the query tabs are saved with the report, so the tests stay with the measures they test (Microsoft, 2025a).

Treat these queries as part of the model’s documentation. A tab named Tests that holds one query per control total, each with a comment saying what it should return, is the Power BI counterpart of the control totals of Chapter 4 and the audit query library of Chapter 12. Anyone who changes the model can run the tab again and see at once whether a number moved. Figure 14.6 shows the first test of Tutorial 14.2 after it has run.

Figure 14.6: A Test in DAX Query View.

14.9 Guided Tutorial 14.2: Writing and Testing the Margin Measures

Context and objective. In this tutorial, you will write the measures that answer the managers’ questions about margin and discounts, and test them. You will collect the measures in a table of their own, build the sales measures from simple sums to a ratio with a variable, use CALCULATE to fix a filter and to remove one, and show Furniture’s margin by quarter. Finally, you will write a tab of tests in DAX query view that compares the measures with the totals of Chapter 6.

Prerequisites. You need Charles River Reports.pbix from Tutorial 14.1, with the sales star and the marked Date table. If you start here, download Charles River Reports as it stands at the end of Tutorial 14.1 from Companion Files: a Power BI project that you point at your copy of CharlesRiver.xlsx, refresh, and save as Charles River Reports.pbix, as that page explains.

Step-by-step instructions.

Step 1. Create a table for the measures.

1. Choose Home > Enter data.
2. Type Key Measures as the table’s name (Power BI Desktop does not accept a table named Measures), leave the single empty column as it is, and click Load.

The Key Measures table appears in the Data pane with one column, Column1. You will hide that column once the table holds its first measure, and the table will then move to the top of the Data pane.

Step 2. Write the base measures. Each measure is a named sum or count of the fact table.

1. Select the Key Measures table in the Data pane, and choose Modeling > New measure.
2. Type the first definition below in the formula bar, and press Enter.
3. Repeat the two actions for each of the other definitions.
4. Select each new measure, and on the Measure tools tab, set its format: the thousands separator and two decimals for amounts, and a whole number for the two counts.
5. Right-click Column1 of the Key Measures table, and choose Hide in report view.

Revenue = SUM ( SalesInvoiceLine[LineTotal] )
List Amount = SUM ( SalesInvoiceLine[ListAmount] )
Discounts = SUM ( SalesInvoiceLine[DiscountAmount] )
Invoice Lines = COUNTROWS ( SalesInvoiceLine )
Invoices = DISTINCTCOUNT ( SalesInvoiceLine[SalesInvoiceID] )

Each line is a separate measure, typed one at a time, and with Column1 hidden the Key Measures table moves to the top of the Data pane. Invoices counts the distinct SalesInvoiceID values among the lines in the filter context, so it now follows any selection on Item, Customer, or Date.

Step 3. Write the standard cost and the margin. Standard cost needs an iterator, because the cost per unit lives on the Item table.

1. Add the measures below to the Key Measures table, as in Step 2.
2. Format Standard Cost and Gross Margin as amounts, and Margin % as a percentage with two decimals.

Standard Cost =
SUMX (
    SalesInvoiceLine,
    SalesInvoiceLine[Quantity] * RELATED ( 'Item'[StandardCost] )
)

Gross Margin = [Revenue] - [Standard Cost]

Margin % =
VAR Rev = [Revenue]
RETURN
    DIVIDE ( Rev - [Standard Cost], Rev )

In the formula bar, Shift+Enter starts a new line within a measure, and Enter commits it. Margin % is a ratio of totals. Like any measure, it is calculated for each cell, and it divides one total by another, which an implicit measure, the summary of a single column, cannot do.

Step 4. Write the discount rate. The rate is discount dollars divided by the dollars before discount, the ratio that Average of Discount misstated in Chapter 13.

1. Add the two measures below.
2. Format Discount Rate as a percentage with two decimals.

Price Before Discount =
SUMX ( SalesInvoiceLine, SalesInvoiceLine[Quantity] * SalesInvoiceLine[UnitPrice] )

Discount Rate = DIVIDE ( [Discounts], [Price Before Discount] )

Price Before Discount multiplies each line’s quantity by its unit price, so the rate divides the discount dollars by the dollars they were taken from, and each line counts in proportion to its value. For fiscal 2026 the rate is well above the average of the Discount column that Chapter 13 found misleading, because the discounted lines are larger than the average line.

Step 5. Use CALCULATE to fix one filter and remove another.

1. Add the two measures below, and format Furniture Revenue as an amount and Share of Revenue as a percentage.
2. On a new page named Margins, add a table visual with ItemGroup from Item and the measures Revenue, Furniture Revenue, and Share of Revenue.
3. Add a slicer with Year from the Date table, and select 2026.

Furniture Revenue = CALCULATE ( [Revenue], 'Item'[ItemGroup] = "Furniture" )

Share of Revenue =
DIVIDE ( [Revenue], CALCULATE ( [Revenue], REMOVEFILTERS ( 'Item' ) ) )

Furniture Revenue shows the same amount on every row, because its filter replaces the row’s item group. Share of Revenue gives each group’s share of fiscal 2026, with Furniture holding more than half, because it removes the filter on Item but keeps the year.

Step 6. Show Furniture’s margin by quarter.

1. Add a matrix with ItemGroup from Item on Rows, YearQuarter from Date on Columns, and Margin % as Values.
2. Filter the matrix to the years 2025 and 2026 in the Filters on this visual section.
3. Select the year slicer, choose Format > Edit interactions, and click the None icon above the matrix, so that the slicer’s 2026 does not hide the quarters of 2025.
4. Read the Furniture row across the eight quarters.

Figure 14.7 shows the matrix. Furniture’s margin at standard cost holds steady through the first half of 2026, falls in the third quarter, and falls again in the fourth, to its lowest level in the two years, the decline the controller asked about in Part I. Each total is a margin of its own, calculated from the totals of revenue and cost, not an average of the cells beside it.

Figure 14.7: Margin at Standard Cost by Item Group and Quarter.

Step 7. Write the first test. Each test is a query whose result must equal a total you already trust.

1. Select DAX query view in the bar on the left.
2. Double-click the query tab and rename it Tests.
3. Type the query below with its comment line, select it, and click Run.
4. Record the result.

// Test 1: fiscal 2026 revenue and lines must equal Chapter 6's control totals
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Year],
    "Lines", [Invoice Lines],
    "Revenue", ROUND ( [Revenue], 2 ),
    "Gross Margin", ROUND ( [Gross Margin], 2 ),
    "Margin %", ROUND ( [Margin %], 4 )
)
ORDER BY 'Date'[Year]

The test rounds its amounts to the cent and its ratio to four decimals, because the results grid ignores the measures’ formats. Its 2026 row should equal Chapter 6’s invoice line count and line total, as in Figure 14.6.

Step 8. Write the second test.

1. Type the query below under the first, with its comment line.
2. Select it, and click Run.

// Test 2: Furniture margin by quarter must equal the margins of Chapter 6's bridge
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[YearQuarter],
    TREATAS ( { "Furniture" }, 'Item'[ItemGroup] ),
    TREATAS ( { 2026 }, 'Date'[Year] ),
    "Revenue", ROUND ( [Revenue], 2 ),
    "Gross Margin", ROUND ( [Gross Margin], 2 ),
    "Margin %", ROUND ( [Margin %], 4 )
)
ORDER BY 'Date'[YearQuarter]

TREATAS applies a list of values as a filter on a column, here Furniture and fiscal 2026. The result should show the Furniture margin of the third and fourth quarters that Chapter 6’s bridge started from and ended at. A test that fails is a finding about the model, not about the business: look for a missing relationship, a filter that reached the wrong table, or a measure that summed what it should have iterated.

Step 9. Document the measures.

1. Select Model view, and select Revenue in the Key Measures table.
2. In the Properties pane, type a description, such as Invoiced revenue: the sum of LineTotal on the invoice lines.
3. Add a description to each of the other measures.
4. On the Validation page, add Invoices, Standard Cost, and Margin % to the table of totals, and save the report.

A reader who points at a measure in the Data pane now sees what it means, and the Validation page shows the new measures beside the control totals they must agree with.

Checkpoint. Your model should now pass these checks:

• The Key Measures table holds the sales measures, each with a format and a description, and appears at the top of the Data pane.
• Test 1’s fiscal 2026 row equals Chapter 6’s control totals, and Test 2 reproduces the Furniture margins at the two ends of Chapter 6’s bridge.
• Discount Rate is higher than the Average of Discount of Chapter 13, and Share of Revenue adds up to 100% across the item groups of a year, though the rounded shares can add up to a hundredth more or less.

The measures now say what the report’s numbers mean, and the tests check them against the totals of Chapter 6. The fall in Furniture’s margin is visible quarter by quarter. Comparing it with the same quarter of the year before needs time intelligence, which the next section explains and Exercise 14.3 builds.

14.10 Time Intelligence

Comparisons across periods are among the first questions any accounting report must answer: this month against the same month last year, the year to date, the trend over the last few months. DAX’s time intelligence functions build them by shifting or extending the dates in the filter context. They take the date column of the marked date table as their argument. Table 14.4 lists the ones this Part uses, and where.

Table 14.4: Time intelligence functions of this Part and where they are used

Function	Returns, for the dates in the filter context	Used in
TOTALYTD	A measure’s total for the year to date, from January 1 unless another year-end is given	Revenue YTD, Exercise 14.3
SAMEPERIODLASTYEAR	The same dates one year earlier	Revenue PY, Exercise 14.3
DATEADD	The dates shifted by a number of days, months, quarters, or years	The prior quarter, Exercise 14.4
DATESBETWEEN	The dates between two dates, inclusive	The three months before, Tutorial 14.3
DATESINPERIOD	A period of a given length ending on a date	The trailing twelve months, Chapter 15

CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) returns, for a cell filtered to October 2026, the revenue of October 2025, and the change against the prior year divides the difference by that value. For any period of fiscal 2024, the prior year is blank, because the data begins in January 2024, and the change is blank too, which DIVIDE handles without an error. A prior-year measure also returns values for the months of 2027 in the date table, the prior year of which is 2026. A visual that shows such a measure should be filtered to the years with data, or the reader sees a 2027 row with a prior year and no current year.

These functions are the model-level counterpart of the window functions of Chapter 11, which compared each row with earlier rows in SQL. Power BI also offers visual calculations, which compute running sums and moving averages from the values a visual already shows, without adding a measure to the model. Chapter 15 uses them. Power BI’s newer calendar-based time intelligence, which adds week-based calculations and calendars that the classic functions cannot follow, such as a retail 4-4-5 calendar, was still a preview feature in September 2026 (Microsoft, 2025b). This book uses the classic functions, which work for any calendar that follows the months of the year, whether its fiscal year ends in December, as Charles River’s does, or in another month.

TipIn Practice

An expectation is only useful if it is independent of the amount it is compared with. A measure that compares each month’s expense with the average of the three months before it, not including the month itself, is a simple analytical procedure of the kind Chapter 6 described: the month cannot hide a spike by raising its own expectation. After every refresh, the same measure is evaluated against the new data, which turns a one-time check into a monitoring tool.

14.11 Modeling the General Ledger

The ledger forms a second star. GLEntry is a fact table with one row for each posting, and Account, CostCenter, and Date are its dimensions. Because Date is shared, a single year slicer filters the sales lines and the ledger together, and a page can place revenue from the invoices beside revenue from the ledger, as Chapter 6 reconciled them in a workbook. Figure 14.8 shows the two stars in one model.

Figure 14.8: Two Fact Tables Sharing One Date Table.

Two accounting facts shape the measures. The first is the closing entries. As Chapter 6 explained, the entries that close each year move the balances of the revenue and expense accounts to retained earnings on December 31. In the ledger they are ordinary postings, so a measure that adds up all the postings to the revenue and expense accounts nets them to zero for every year, and an income statement built on it shows a net income of zero. Figure 14.9 shows the effect. The closing entries are journal entries whose type begins with “Year-End Close”, and GLEntry’s VoucherNumber holds the journal entry’s number, so a merge with the JournalEntry table in Power Query brings the type onto each posting and a custom column flags the closes. The base measure of the ledger then excludes the flagged postings.

Figure 14.9: The Income Statement With and Without the Closing Entries.

The second is the sign. The ledger stores debits and credits in separate columns, and debits less credits is positive for assets and expenses and negative for liabilities, equity, and revenue, the accounts whose normal balance is a credit. An income statement reads best with revenue positive and expenses negative, so that the total of the column is net income. The P&L Amount measure of Tutorial 14.3 reverses the sign of debits less credits, which gives exactly that, and grouping it by the Account table’s AccountType and AccountSubType reproduces the income statement of Chapter 6, now interactive, and by month as well as by year. For a balance sheet, the balance is cumulative from the first posting to the date in the filter context, and debits less credits leaves assets positive and liabilities and equity negative. A statement reverses the sign by account type, never account by account by NormalBalance, which would add contra accounts such as accumulated depreciation to the totals they reduce. Exercise 14.1 builds the balance sheet and lists the contra accounts.

NoteConnecting the Dots

Every report in this book that touches the ledger has had to answer the same two questions: which postings belong to the period, and what sign each account carries. Chapter 6 answered them with Power Query and a pre-closing trial balance, Chapter 10 with a WHERE clause that left out the closing entries, and Exercise 11.1 with CASE. In Power BI the answers become a column in the model and a measure that every visual inherits, which is what makes a model a control as well as a convenience.

14.12 Guided Tutorial 14.3: Modeling the Ledger and the Income Statement

Context and objective. In this tutorial, you will add the general ledger to the model as a second star that shares the Date table. You will flag the year-end closing entries in Power Query, write the ledger’s measures, and build an income statement by year that ties to the net income of each closing entry. Then you will use time intelligence to compare each month’s operating expenses with the months before it, and add a trial-balance test to the Tests tab.

Prerequisites. You need Charles River Reports.pbix from Tutorials 14.1 and 14.2. If you start here, download Charles River Reports as it stands at the end of Tutorial 14.2 from Companion Files: a Power BI project that you point at your copy of CharlesRiver.xlsx, refresh, and save as Charles River Reports.pbix, as that page explains. GLEntry holds hundreds of thousands of rows, so loading it takes several minutes.

Step-by-step instructions.

Step 1. Choose the ledger’s Tables.

1. Choose Home > Get data > Excel workbook, and select CharlesRiver.xlsx.
2. In the Navigator, select T3_GLEntry, T1_Account, T75_CostCenter, and T2_JournalEntry, and click Transform Data.
3. Rename the queries GLEntry, Account, CostCenter, and JournalEntry.

As in Part II, the ledger is opened with Transform Data, never Load, so that it is shaped before any of its rows reach the model.

Step 2. Keep the columns the model needs.

1. In GLEntry, choose Home > Choose Columns and keep GLEntryID, PostingDate, AccountID, Debit, Credit, VoucherNumber, SourceDocumentType, and CostCenterID, and set PostingDate’s data type to Date if it shows a time.
2. In Account, keep AccountID, AccountNumber, AccountName, AccountType, AccountSubType, and NormalBalance.
3. In CostCenter, keep CostCenterID and CostCenterName.
4. In JournalEntry, keep EntryNumber and EntryType.

The ledger keeps every posting, the closing entries included. Step 3 marks the closing entries rather than removing them, so that each measure can decide whether to leave them out.

Step 3. Flag the closing entries.

1. Select GLEntry, choose Home > Merge Queries, and merge it with JournalEntry on VoucherNumber and EntryNumber, with a left outer join.
2. Expand only EntryType, without the prefix.
3. Choose Add Column > Custom Column, name the column IsYearEndClose, enter the formula below, and set the column’s type to True/False.
4. Right-click JournalEntry and clear Enable load, and choose Home > Close & Apply.

[EntryType] <> null and Text.StartsWith([EntryType], "Year-End Close")

Only journal entries have a VoucherNumber equal to an EntryNumber, so every other posting finds no match and gets a null EntryType and a false flag. The postings of six journal entries are flagged, two closing entries for each fiscal year.

Step 4. Relate the ledger to its dimensions.

1. In Model view, drag AccountID from GLEntry onto AccountID in Account.
2. Drag CostCenterID from GLEntry onto CostCenterID in CostCenter.
3. Drag PostingDate from GLEntry onto Date in the Date table.
4. Select GLEntryID, AccountID, and CostCenterID in GLEntry, and in the Properties pane turn on Is hidden and set Summarize by to None, as in Tutorial 14.1.

The Date table now filters two fact tables, the invoice lines and the ledger, as in Figure 14.8.

Step 5. Write the ledger’s measures.

1. Add the measures below to the Key Measures table, and format each as an amount.

GL Amount =
CALCULATE (
    SUM ( GLEntry[Debit] ) - SUM ( GLEntry[Credit] ),
    GLEntry[IsYearEndClose] = FALSE ()
)

P&L Amount = - [GL Amount]

Net Income =
CALCULATE ( [P&L Amount], Account[AccountType] IN { "Revenue", "Expense" } )

GL Amount is debits less credits without the closing entries. P&L Amount reverses its sign, so revenue reads positive and expenses negative, and Net Income adds it up over the revenue and expense accounts.

Step 6. Build the income statement by year.

1. On a new page named Income Statement, add a matrix with AccountType and then AccountSubType from Account on Rows, Year from Date on Columns, and P&L Amount as Values.
2. In Filters on this visual, keep the AccountType values Revenue and Expense, and the years 2024 to 2026.
3. Add a card with Net Income, and in Filters on this visual keep the year 2026 for the card.
4. Read each year’s total in the matrix, and compare the 2026 total with the card.

Figure 14.10 shows the matrix. Each year’s total is that year’s net income, and it equals the amount the year’s closing entry transferred to retained earnings. With YearMonth in place of Year on Columns, the same matrix is the statement month by month, because P&L Amount is calculated for whatever period a cell holds. To see why the flag matters, add the temporary measure below, which adds up all the postings, and place it in the matrix. It shows zero for every year, as in Figure 14.9. Delete it afterward.

Net Income with Closes =
CALCULATE (
    SUM ( GLEntry[Credit] ) - SUM ( GLEntry[Debit] ),
    Account[AccountType] IN { "Revenue", "Expense" }
)

Figure 14.10: The Income Statement by Year.

Step 7. Write the expectation. An expectation built from the three months before a month, not including it, cannot hide a spike in the month itself.

1. Add the measures below to the Key Measures table.
2. Format the first two as amounts and the third as a percentage with one decimal.

Operating Expense =
CALCULATE ( [GL Amount], Account[AccountSubType] = "Operating Expense" )

Expected Operating Expense =
VAR MonthStart = MIN ( 'Date'[Date] )
VAR PriorMonths =
    DATESBETWEEN ( 'Date'[Date], EDATE ( MonthStart, -3 ), MonthStart - 1 )
VAR MonthsInWindow =
    COUNTROWS ( CALCULATETABLE ( VALUES ( 'Date'[YearMonth] ), PriorMonths ) )
RETURN
    IF (
        HASONEVALUE ( 'Date'[YearMonth] )
            && NOT ISBLANK ( [Operating Expense] )
            && MonthsInWindow > 0,
        DIVIDE ( CALCULATE ( [Operating Expense], PriorMonths ), MonthsInWindow )
    )

Operating Expense Deviation =
DIVIDE (
    [Operating Expense] - [Expected Operating Expense],
    [Expected Operating Expense]
)

The second measure is worth reading slowly. MonthStart is the first day of the month in the filter context. PriorMonths is the set of dates in the three months before it, which DATESBETWEEN takes from the date table, so for the first months of 2024 the window holds fewer than three months, or none. MonthsInWindow counts the months the window actually holds, and the result divides the expense of those months by their number. The condition makes the measure blank unless the filter context is a single month (HASONEVALUE tests that), so a total row never compares a year with one month, and blank as well when there is no expense to compare or no earlier month.

Step 8. Chart the expense against its expectation.

1. Add a line chart with YearMonth from Date on the axis and Operating Expense and Expected Operating Expense as values, filtered to the years 2024 to 2026.
2. Add a table visual with YearMonth and Operating Expense Deviation, sorted from the largest deviation.
3. Record the months whose deviation exceeds 20%.

Figure 14.11 shows the line chart. Apart from February 2024, whose expectation rests on the start-up month alone, the months well above their expectation are the six with three pay dates, from March 2024 on, which Chapter 11 traced to payroll recorded when paid rather than accrued. March 2024 stands out most, because its expectation averages only the two months before it, the start-up month among them.

Figure 14.11: Monthly Operating Expense Against the Average of the Three Months Before.

Step 9. Add the trial-balance test.

1. In DAX query view, open the Tests tab, and add the query below at the end.
2. Run it, and compare its result with Chapter 6’s pre-closing trial balance.
3. Save the report.

// Test 3: the pre-closing trial balance at 2026-12-31 must balance at Chapter 6's total
EVALUATE
VAR Balances =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( Account[AccountID] ),
            "Balance", CALCULATE ( SUM ( GLEntry[Debit] ) - SUM ( GLEntry[Credit] ) )
        ),
        'Date'[Date] <= DATE ( 2026, 12, 31 ),
        NOT GLEntry[VoucherNumber] IN { "JE-2026-000296", "JE-2026-000297" }
    )
RETURN
    ROW (
        "Accounts", COUNTROWS ( FILTER ( Balances, NOT ISBLANK ( [Balance] ) ) ),
        "Debit balances", ROUND ( SUMX ( FILTER ( Balances, [Balance] > 0 ), [Balance] ), 2 ),
        "Credit balances", ROUND ( -SUMX ( FILTER ( Balances, [Balance] < 0 ), [Balance] ), 2 )
    )

The query keeps every posting through December 31, 2026, except the two closing entries of 2026, as Chapter 6’s pre-closing trial balance did. It calculates each account’s balance and adds up the debit balances and the credit balances separately. The two totals must be equal, and equal to Chapter 6’s.

Checkpoint. Before you build reports on the model, confirm the following:

• The model has two fact tables, SalesInvoiceLine and GLEntry, sharing the Date table, and the closing entries are flagged.
• The income statement’s total for each fiscal year equals that year’s net income, and a measure that keeps the closing entries shows zero.
• The months whose operating expense deviates most from its expectation are February 2024 and the six months with three pay dates.
• Test 3 returns equal debit and credit balances, equal to Chapter 6’s pre-closing trial balance.

The model now answers both the sales questions and the controller’s. Its two stars share one calendar, its measures exclude the closing entries once for every visual, and its tests show that it agrees with the workbook and the queries of the earlier Parts.

14.13 Looking Ahead

This chapter has turned the report of Chapter 13 into a model. You designed a star schema, named the grain of its fact tables, and flattened the invoice header into its lines, so that every dimension relates to the fact table directly. You set relationships to filter in one direction, learned why a second date needs an inactive relationship, and built and marked a date table that the sales lines and the ledger share. You wrote explicit measures with SUM, DIVIDE, SUMX, and RELATED, and learned to read them in their filter and row contexts, including the context transition that happens whenever a measure is used inside a row. You changed the filter context with CALCULATE, compared periods with time intelligence, and modeled the ledger so that the closing entries and the signs of the accounts are handled once for every visual. Each measure was tested in DAX query view against a total you already trusted. Most of this work was descriptive analytics, in the terms of Table 1.1, but the comparisons it makes possible, a margin against the same quarter last year and an expense against its own recent history, are the first steps of diagnosis.

The model now holds every number the managers asked for, and the controller’s income statement ties to the ledger by year and can be read by month. What it does not yet have is reports designed for the people who read them. Chapter 15 builds those reports on this model, with the visual calculations this chapter left aside.

14.14 Chapter Summary

A semantic model is easiest to use and most reliable when it is arranged as a star schema. Fact tables record events at a stated grain, one row for each event, with the keys of the things involved and the amounts to be added up. Dimension tables describe the things, one row for each, with the attributes a reader filters and groups by. The invoice header and its lines, designed in the database to avoid repeating facts, form a snowflake in Power BI, in which the customer reaches the lines only through the header. Merging the header’s date, number, and customer key into the lines turns them into one fact table that every dimension filters directly. The invoice number stays in the fact table as a degenerate dimension. The restructured model is accepted only when the Validation page shows the same totals as before.

Relationships in a star are many to one, from the fact table to each dimension, with a single cross filter direction. Setting a relationship to Both can make a number respond to a click while changing what it means, and in a model with two fact tables it can carry a selection from one fact table into the other. Only one path between two tables can be active, so a second date, such as the date a line shipped, is related through an inactive relationship that a measure switches on with USERELATIONSHIP. A date table with one row for every day of full years, marked as the model’s date table, gives every visual one calendar and lets the classic time intelligence functions work. Date and Item are reserved words in DAX, so those tables are written in single quotes.

Explicit measures, written in DAX, name each calculation once, carry their own format and description, and can be used in other measures. A calculated column is stored for each row at refresh, while a measure is calculated for each cell from the rows that the cell’s filters keep. Every formula is evaluated in a context. The filter context is the set of filters on a cell, which the relationships carry to the fact table. The row context is the current row of an iterator such as SUMX, where RELATED reaches the one side of a relationship, which is how standard cost is calculated from each line’s quantity and its item’s cost. CALCULATE changes the filter context: its filter arguments replace existing filters on the same columns, and REMOVEFILTERS removes them, so a share of revenue removes the filter on Item but keeps the year. Used inside a row context, CALCULATE turns the current row into a filter, which is context transition.

DAX query view runs queries against the model without building a visual. A tab of tests, each a query whose result must equal a control total from an earlier chapter, documents the model and shows at once whether a change moved a number. Time intelligence functions such as SAMEPERIODLASTYEAR, TOTALYTD, and DATESBETWEEN shift or extend the dates in the filter context. The prior year is blank for fiscal 2024, and an expectation built from the months before a month, not including it, cannot be raised by a spike in the month itself.

The general ledger forms a second star, with Account, CostCenter, and the shared Date table as its dimensions. Its year-end closing entries move each year’s revenue and expenses to retained earnings, so a measure over all postings shows a net income of zero. A flag brought from the JournalEntry table in Power Query lets the ledger’s base measure exclude the closes. Reversing the sign of debits less credits makes revenue positive and expenses negative, so the income statement by year ties to the net income each closing entry transferred, and the pre-closing trial balance in DAX query view balances at the total of Chapter 6.

14.15 Key Terms

Calculated column. A column of an Excel Table whose formula Excel applies by default to every row, including rows added later. In a Power BI model, a calculated column is a column added to a table with a DAX formula, evaluated for each row when the model is refreshed and stored like any other column, so it can be used to filter, group, or sort.

Context transition. The conversion of the current row of a row context into an equivalent filter context, which CALCULATE performs. It happens whenever CALCULATE is used, or a measure is referred to, inside a row context, such as a calculated column or an iterator.

Cross filter direction. The setting of a Power BI relationship that decides which way filters travel along it. With Single, the default for a many-to-one relationship, a filter travels from the one side to the many side only; with Both, it travels in both directions.

DAX. Data Analysis Expressions, the formula language of Power BI’s measures, calculated columns, and calculated tables, and of the queries run in DAX query view.

DAX query view. The view of Power BI Desktop in which DAX queries are written, run, and saved with the report, and in which their results appear in a grid without a visual being built.

Date table. A dimension table with one row for every day of the period a model covers and columns for the ways the business groups days, such as year, quarter, and month. Marked as the model’s date table, it is the calendar that the classic time intelligence functions rely on.

Degenerate dimension. An attribute, such as an invoice number, that is kept in a fact table and used to filter or group, without a dimension table of its own.

Dimension table. A table of a star schema that describes one kind of thing, such as items, customers, or dates, one row for each, with the attributes a reader filters and groups by.

Fact table. A table of a star schema that records events or transactions, one row for each event, with the keys of the dimensions involved and the amounts to be added up.

Filter context. The set of filters in effect where a DAX formula is evaluated, such as the row and column of a matrix cell, the page’s slicers, and the filters of the Filters pane. Relationships carry the filters from the dimension tables to the fact tables.

Grain. The event that one row of a fact table represents, such as one line of one invoice or one posting to an account. The grain decides which questions a fact table can answer.

Inactive relationship. A relationship that a model holds but does not use to filter, drawn as a dashed line in Model view. A measure switches it on for its own calculation with USERELATIONSHIP, which is how one date table can serve a second date.

Iterator. A DAX function that evaluates an expression for each row of a table and combines the results, such as SUMX. It creates a row context for each row it evaluates.

Measure. A named DAX calculation that is defined once in a model and evaluated for each cell of a visual from the rows that the cell’s filter context keeps. Its result is never stored, and it can be used inside other measures.

Row context. The current row of a table during a DAX evaluation that works through the table row by row, as in a calculated column or an iterator. A row context does not filter other tables by itself.

Snowflake schema. An arrangement of tables in which a dimension reaches the fact table only through another table, as a customer reaches the invoice lines through the invoice header.

Star schema. An arrangement of a model in which fact tables are related directly to the dimension tables that describe them, many to one, so that each fact table sits at the center of its dimensions.

Time intelligence. The DAX functions that shift or extend the dates in the filter context, such as SAMEPERIODLASTYEAR and TOTALYTD, to compare periods or accumulate them. The classic functions, which this book uses, rely on a marked date table.

14.16 Multiple Choice Questions

1. Which statement gives the grain of the SalesInvoiceLine fact table?

1. One line of one invoice
2. One invoice with its total
3. One item in one fiscal quarter
4. One customer in one month

2. In the model of Chapter 13, Customer filters SalesInvoice, and SalesInvoice filters SalesInvoiceLine. In the terms of this chapter, what is this arrangement?

1. A star with two fact tables that share the Customer dimension
2. A many-to-many relationship that runs through a junction table
3. A snowflake, with Customer reached through the header
4. A degenerate dimension stored inside the invoice line fact table

3. After the header is flattened into the lines, InvoiceNumber stays in SalesInvoiceLine and is used to filter and group, with no table of its own. What is it called?

1. A role-playing dimension
2. A degenerate dimension
3. A calculated table
4. A surrogate key

4. A colleague sets the relationship between Date and SalesInvoiceLine to Both, so that a date slicer lists only the days with sales. In the model of this chapter, what is the main risk?

1. The Date table can no longer be marked as the model’s date table
2. The relationship becomes inactive until a measure switches it on
3. Every measure that uses DIVIDE begins to return an error
4. A selection on Item can then filter the ledger through Date

5. A fact table of invoice lines has an InvoiceDate and a ShipmentDate, and the Date table is related to both. How does a measure calculate revenue by ship date?

1. By setting the ship-date relationship to Both
2. By adding a second date table filtered with FILTER
3. By using USERELATIONSHIP inside CALCULATE
4. By sorting Date by the ShipmentDate column

6. The Charles River ledger runs from January 1, 2024, into February 2027. Which date table follows Microsoft’s guidance?

1. One row for each day from January 1, 2024, to December 31, 2027
2. One row for each invoice date that appears in the invoice lines
3. One row for each month from January 2024 to February 2027
4. One row for each day from January 1, 2024, through February 23, 2027

7. Which of the following belongs in a measure rather than in a calculated column?

1. The product type taken from the code of each item
2. The margin percentage of each cell in a matrix
3. The list amount of each invoice line
4. A flag on each posting of a closing entry

8. The invoice lines hold the quantity, and the Item table holds the standard cost per unit. How should a measure calculate the standard cost of the lines in the filter context?

1. Multiply the total quantity by the total of the items’ standard costs
2. Multiply the total quantity by the average standard cost of all items
3. Use RELATED in the measure itself, without an iterator around it
4. Iterate the lines with SUMX, multiplying quantity by RELATED cost

9. A matrix shows revenue by item group on rows and by quarter on columns, and a slicer on the page is set to the Wholesale segment. Which filters make up the filter context of the Furniture cell for 2026-Q4?

1. Only the item group and the quarter of the cell’s row and column
2. The item group, the quarter, and the Wholesale segment
3. Only the slicer, because rows and columns group without filtering
4. None, because measures ignore filters unless CALCULATE is used

10. In a calculated column on the Customer table, SUM ( SalesInvoiceLine[LineTotal] ) returns the same large amount on every row. Why?

1. The relationship from Customer to the invoice lines is inactive
2. Calculated columns are evaluated before relationships exist
3. SUM ignores relationships unless its column is hidden
4. The row context does not filter the lines without CALCULATE

11. A table visual by item group shows the measure CALCULATE ( [Revenue], 'Item'[ItemGroup] = "Furniture" ). What does the Lighting row show?

1. Furniture’s revenue, because the filter replaces Lighting
2. Lighting’s revenue, because the row’s filter takes priority
3. A blank, because the two item groups cannot both apply
4. Zero, because the two filters on ItemGroup intersect

12. A share measure divides the revenue of each item group by CALCULATE ( [Revenue], ALL ( SalesInvoiceLine ) ). With a year slicer set to 2026, Furniture’s share reads about a fifth. What is wrong?

1. ALL ignores the relationship between Item and the invoice lines
2. DIVIDE rounds its result down whenever a slicer is applied
3. Removing the filters from the fact table also removes the year
4. A share should be calculated with AVERAGE instead of DIVIDE

13. What makes a query in the Tests tab of DAX query view a useful test of the model?

1. It runs faster than the visuals that show the same measures on a page
2. Its result is compared with a control total from another source
3. It is saved in a separate file that the report cannot change
4. It replaces the measure in every visual of the report

14. A matrix by year shows CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) next to Revenue. Which year shows a prior-year value but no revenue?

1. 2024, because the data begins in January 2024
2. 2025, because 2024 was a start-up year
3. 2027, because the date table runs through 2027
4. None, because the function skips years without data

15. An income statement built on all the postings to the revenue and expense accounts shows a net income of zero in every year. What explains it?

1. The year-end closes reverse each year’s revenue and expenses
2. Revenue is stored as credits, which cancel the debits of the expenses
3. The Date table is not marked, so the years are not kept apart
4. The relationship between GLEntry and Account is set to Both

14.17 Applied Exercises

Work on these exercises in a copy of Charles River Reports.pbix, saved with File > Save as after you finish this chapter’s tutorials, such as Ch14 Exercises.pbix. Build each exercise on its own report page, or in its own query tab in DAX query view, named after it (such as Ex 14.1), and put the measures it asks for in a display folder of the Key Measures table with the same name (in Model view, select a measure and type the folder’s name in Display folder in the Properties pane). Do not change the queries, relationships, measures, or pages the tutorials created, except where an exercise tells you to change something temporarily and undo it. When an exercise needs a Table the tutorials did not load, import it under the query name the exercise gives.

14.17.1 Financial Accounting Exercises

Exercise 14.1: The Balance Sheet at Any Month-End

Table group: Accounting Core

Scenario. The controller wants a balance sheet beside the income statement of Tutorial 14.3, at the end of whichever month the reader selects and before that year’s closing entries, so that it can be compared with the pre-closing balance sheet of Chapter 6.

Requirements.

1. Add the measure below. A balance is cumulative, so the measure removes the filters on the Date table and keeps every posting up to the last day in the filter context. Explain in one sentence each what its three filter arguments do, and why the closing entries are left out only when they are posted on that day.

Balance =
VAR AsOf = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( GLEntry[Debit] ) - SUM ( GLEntry[Credit] ),
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] <= AsOf,
        NOT ( GLEntry[IsYearEndClose] && GLEntry[PostingDate] = AsOf )
    )

2. Build a matrix with AccountType and AccountSubType from Account on Rows and Balance as Values, and a slicer with YearMonth set to single selection. Select 2026-12 and confirm three things: the total of the matrix is zero; the assets equal the liabilities, the equity, and the balance of the revenue and expense accounts, each with its sign reversed; and that last balance, with its sign reversed, equals the net income of fiscal 2026 from Tutorial 14.3.

3. Select 2026-06, and then 2024-12. Confirm that the matrix still totals zero in both, and explain why the revenue and expense accounts at June 2026 hold only six months of postings, although the ledger begins in January 2024.

4. Write two variants of the measure: one with no filter on the closing entries, and one built on GL Amount, which leaves out all of them. At 2026-12, compare the equity and the revenue and expense balances of the three measures. Say which variant gives a post-closing balance sheet, and what is wrong with the other.

5. In a table visual, list the accounts whose NormalBalance is the opposite of the usual side for their AccountType (debit for assets and expenses; credit for liabilities, equity, and revenue). Explain why a statement whose signs followed each account’s NormalBalance would misstate its subtotals.

Deliverable. The page with the matrix, the two variant measures, and a short note answering requirements (1) and (3) to (5).

Exercise 14.2: Revenue by Invoice Date and by Ship Date

Table group: Order-to-Cash

Scenario. Chapter 12’s auditor found invoices posted in the year after the goods were delivered. The controller wants the report to show each year’s revenue by invoice date beside its revenue by the date the goods shipped, and to list the invoice lines that cross from one year to the next.

Requirements.

1. Import T14_Shipment as a query named ShipmentHeaders, keeping ShipmentID and ShipmentDate, and T15_ShipmentLine as ShipmentLines, keeping ShipmentLineID and ShipmentID. Clear Enable load on both.

2. Import T17_SalesInvoiceLine again as ShippedLines, keeping SalesInvoiceLineID, SalesInvoiceID, ItemID, ShipmentLineID, and LineTotal. Merge it with SalesInvoice to bring InvoiceNumber and InvoiceDate, with ShipmentLines to bring ShipmentID, and then with ShipmentHeaders to bring ShipmentDate, each a left outer join expanded without the prefix. Set both dates to the Date type.

3. Relate ShippedLines to Item on ItemID and to Date on InvoiceDate. Then drag ShipmentDate from ShippedLines onto Date in the Date table. Confirm that Power BI drew the second relationship as a dashed line, and explain why it cannot be active.

4. Add the measure Shipped Revenue = SUM ( ShippedLines[LineTotal] ) and the measure below, and build a matrix with Year from Date on Rows and both measures as Values. Confirm that Shipped Revenue equals the revenue of the Validation page in each year, and explain the row labeled (Blank).

Revenue by Ship Date =
CALCULATE (
    [Shipped Revenue],
    USERELATIONSHIP ( ShippedLines[ShipmentDate], 'Date'[Date] )
)

5. Add a custom column named CrossesYear to ShippedLines, true when ShipmentDate is not null and its year differs from the year of InvoiceDate. List the lines for which it is true in a table visual with SalesInvoiceLineID, InvoiceNumber, InvoiceDate, ShipmentDate, and LineTotal, and set SalesInvoiceLineID to Don’t summarize in the visual, so that each line has a row of its own. Sort them into two kinds, invoices issued after the year in which the goods shipped and invoices dated before their shipment, and compare each kind with Chapter 12’s revenue cutoff findings.

6. Recommend which date the monthly sales report should use for revenue, and what the report should disclose about the other.

Deliverable. The queries, the matrix and the table visual on the Ex 14.2 page, and a short note answering requirements (3) to (6).

14.17.2 Managerial Accounting Exercises

Exercise 14.3: Monthly Comparisons with the Prior Year

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. The marketing manager wants each item group’s revenue by month beside the same month of the year before, and the year to date beside the prior year to date, for fiscal 2026.

Requirements.

1. Write the measures Revenue PY, with SAMEPERIODLASTYEAR, and Revenue YoY %, the change against it with DIVIDE. Write Revenue YTD, with TOTALYTD, and Revenue PY YTD, which applies SAMEPERIODLASTYEAR to Revenue YTD, and YTD Change %.

2. Build a matrix with ItemGroup on Rows, YearMonth on Columns, and Revenue YoY % as Values, filtered to 2026. Find Furniture’s largest decline and largest increase against the prior year, and compare them with your results for Exercise 11.2, which took revenue from the ledger.

3. For December 2026, compare Revenue YTD with Revenue PY YTD for each item group and in total. Which groups grew in fiscal 2026, and which shrank?

4. Find the item group whose monthly comparisons swing the most. Add a measure that counts the invoice lines of the group in each month, and explain why a percentage change is a weak guide for that group.

5. Build a table visual with Year, Revenue, and Revenue PY and no filter. Explain the row for 2027, and change Revenue PY so that it is blank wherever Revenue is blank.

Deliverable. The measures, the matrix and the table visual on the Ex 14.3 page, and a short note answering requirements (2) to (5).

Exercise 14.4: The Furniture Bridge as Measures

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. Chapter 6’s bridge explained the fall in Furniture’s margin for one pair of quarters, on a worksheet. The controller wants the bridge in the model, so that it can be read for other quarters and item groups, wherever the quarter before has sales.

Requirements.

1. Add the measures Units = SUM ( SalesInvoiceLine[Quantity] ) and Margin PQ = CALCULATE ( [Gross Margin], DATEADD ( 'Date'[Date], -1, QUARTER ) ). DATEADD shifts the dates in the filter context back one quarter.

2. Add the volume effect below, which follows cell B17 of Tutorial 6.3: the change in units at the prior quarter’s margin per unit. Then write Mix Effect, Price List Effect, and Promotion Effect in the same way, following B18 to B20. They use the list margin per unit, ([List Amount] - [Standard Cost]) / [Units], the price-list reduction per unit, ([List Amount] - [Revenue] - [Discounts]) / [Units], and the discount per unit, [Discounts] / [Units], each in the current quarter and in the prior one.

Volume Effect =
VAR PriorQuarter = DATEADD ( 'Date'[Date], -1, QUARTER )
VAR UnitsPQ = CALCULATE ( [Units], PriorQuarter )
VAR MarginPerUnitPQ =
    CALCULATE ( DIVIDE ( [Gross Margin], [Units] ), PriorQuarter )
RETURN
    ( [Units] - UnitsPQ ) * MarginPerUnitPQ

3. Add a Bridge Check measure equal to Margin PQ plus the four effects less Gross Margin. Explain why this model needs no cost effect.

4. Build a matrix with YearQuarter on Rows, filtered to 2026 and to Furniture, and Margin PQ, the four effects, Gross Margin, and Bridge Check as Values. Confirm that the 2026-Q4 row reproduces Chapter 6’s bridge and that Bridge Check is zero in every row.

5. Read the 2026-Q3 row, the change from the second quarter. Which effects explain it, and how does it differ from the fourth quarter’s?

6. Change the item group filter to Lighting. Which effect explains most of Lighting’s change in the fourth quarter of 2026?

Deliverable. The measures and the matrix on the Ex 14.4 page, and a short note answering requirements (3), (5), and (6).

14.17.3 Auditing Exercises

Exercise 14.5: Testing the Model’s Relationships

Table group: Order-to-Cash and Accounting Core

Scenario. Before the report is shared, the internal auditor reviews the model the way Chapter 12 reviewed queries, by testing what each relationship does to the numbers. Rather than change the model, the auditor writes queries that change a relationship for one calculation with CROSSFILTER, a function that sets the cross filter direction of a relationship inside CALCULATE.

Requirements.

1. In a DAX query view tab named Ex 14.5, run the query below. It counts customers three ways for each item group in fiscal 2026: the rows of the Customer table, the distinct customers of the invoice lines, and the rows of the Customer table with the relationship set to Both. Explain the three results, and say which count a report should use.

// Customers by item group, fiscal 2026, counted three ways
DEFINE
    MEASURE Customer[Customer Rows] = COUNTROWS ( Customer )
    MEASURE Customer[Customers Invoiced] =
        DISTINCTCOUNT ( SalesInvoiceLine[CustomerID] )
    MEASURE Customer[Customer Rows Both] =
        CALCULATE (
            COUNTROWS ( Customer ),
            CROSSFILTER ( SalesInvoiceLine[CustomerID], Customer[CustomerID], BOTH )
        )
EVALUATE
SUMMARIZECOLUMNS (
    'Item'[ItemGroup],
    TREATAS ( { 2026 }, 'Date'[Year] ),
    "Customer Rows", [Customer Rows],
    "Customers Invoiced", [Customers Invoiced],
    "Customer Rows Both", [Customer Rows Both]
)

2. Write a query that shows, for each item group in fiscal 2026, Operating Expense, Operating Expense with the relationship between SalesInvoiceLine[InvoiceDate] and Date set to Both, and the number of dates left in the Date table under the same setting. Explain what the Services row means, and how it illustrates the Watch out on two fact tables in this chapter’s section on relationships.

3. Write a query that shows, for each item group in fiscal 2026, the share of revenue calculated with REMOVEFILTERS ( 'Item' ) and with ALL ( SalesInvoiceLine ). Add up each column of shares, and explain what each total is a share of.

4. Write a short list of the relationship and filter settings you would look for when you review a model someone else built, with the test that reveals each.

Deliverable. The query tab and a memo answering requirements (1) to (4).

Exercise 14.6: A Library of Control Totals

Table group: Order-to-Cash and Accounting Core

Scenario. The auditor wants one query that tests whether the model agrees with the control totals established in Parts II and III, can be run again after every change to the model, and reports a pass or a fail for each test.

Requirements.

1. In a query tab named Control Totals, type the query below, which holds two tests, and replace each expected value of 0 with the control total you established in Chapter 6. Then add one ROW for each of these tests, with its expected value from the chapter given: the net income of fiscal 2026 (Tutorial 14.3), the debits of all journal entries in the ledger (Chapter 8), the manufacturing variance posted to account 5080 in fiscal 2026 without the closing entries (Chapter 10), and the revenue posted by sales invoices to accounts 4010 to 4040 and 4080 in fiscal 2026 (Exercise 10.1).

// Control totals: each test must agree within one cent
EVALUATE
VAR Tests =
    UNION (
        ROW ( "Test", "Invoice lines, fiscal 2026", "Expected", 0,
            "Actual", CALCULATE ( [Invoice Lines], 'Date'[Year] = 2026 ) ),
        ROW ( "Test", "Revenue, fiscal 2026", "Expected", 0,
            "Actual", CALCULATE ( [Revenue], 'Date'[Year] = 2026 ) )
    )
RETURN
    ADDCOLUMNS (
        Tests,
        "Difference", ROUND ( [Actual] - [Expected], 2 ),
        "Result", IF ( ABS ( [Actual] - [Expected] ) <= 0.01, "Pass", "Fail" )
    )

2. Run the query. The revenue posted by sales invoices differs from the revenue of the invoice lines. Explain the difference, and add a test that the difference equals its documented cause.

3. In your exercise copy, change GL Amount so that it no longer leaves out the closing entries, run the tests again, and record which fail and which do not. Then undo the change, run them once more, and record the result of that run too, with the restored Net Income card, so that your record has three results in all: before, with the change, and after the undo.

4. Write a conclusion of a few sentences: what the passing tests show about the model, what they cannot show, and when the library should be run.

Deliverable. The Control Totals tab and the conclusion.

14.18 Further Reading

Ferrari, A., & Russo, M. (2026). The definitive guide to DAX: Mastering the semantic model expression language for Microsoft Power BI, Fabric, and Excel (3rd ed.). Microsoft Press. A detailed reference on DAX. Its chapters on evaluation contexts, CALCULATE, and context transition take the ideas of this chapter much further, and the third edition adds the calendar-based time intelligence that this book only names.

Libby, T., Schwebke, J. M., & Goldwater, P. M. (2022). Using data analytics to evaluate the drivers of revenue: An introductory case study using Microsoft Power Pivot and Power BI. Issues in Accounting Education, 37(4), 97–105. https://doi.org/10.2308/issues-2021-057 A teaching case in which you act as a data analyst for a chain of home improvement stores, model ten years of data in Power Pivot or Power BI, and build a dashboard that lets the chief financial officer compare stores and managers by date and by state. It practices data modeling and a dashboard, as this chapter does, on a different business.

Microsoft. (2024b). Bi-directional relationship guidance. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering Microsoft’s explanation of when a relationship should filter in both directions and when it should not, with alternatives that keep the model’s relationships single: a slicer filtered by a measure of the fact table, and a direction changed inside one calculation with CROSSFILTER.

Microsoft. (2024d). Understand star schema and the importance for Power BI. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/guidance/star-schema The design guidance behind the first half of this chapter. It defines fact and dimension tables, explains why Power BI models work best as stars, and covers three cases this chapter met in the Charles River data: a snowflake dimension, which it recommends flattening into one table, a degenerate dimension, and a role-playing date.

Microsoft. (2025a). DAX query view. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/transform-model/dax-query-view The reference for the view in which this chapter’s tests run. It describes the quick queries that write a query for a table or measure, how a measure defined in a query is added to the model, and how query tabs are saved with the report.

O’Leary, D. E. (1999). REAL-D: A schema for data warehouses. Journal of Information Systems, 13(1), 49–62. https://doi.org/10.2308/jis.1999.13.1.49 An accounting information systems view of the star schema. It combines the resources-events-agents model of accounting events with the design of data warehouses, adding time as a dimension so that events roll up from day to month to year, and compares the result with star and snowflake schemas.

14.19 References

Kimball, R., & Ross, M. (2013). The data warehouse toolkit: The definitive guide to dimensional modeling (3rd ed.). Wiley.

Microsoft. (2021a). Avoid using FILTER as a filter argument in DAX. Microsoft Learn. https://learn.microsoft.com/en-us/dax/best-practices/dax-avoid-avoid-filter-as-filter-argument

Microsoft. (2021b). Use variables to improve your DAX formulas. Microsoft Learn. https://learn.microsoft.com/en-us/dax/best-practices/dax-variables

Microsoft. (2024a). Active vs inactive relationship guidance. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

Microsoft. (2024b). Bi-directional relationship guidance. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering

Microsoft. (2024c). Design guidance for date tables in Power BI Desktop. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Microsoft. (2024d). Understand star schema and the importance for Power BI. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Microsoft. (2025a). DAX query view. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/transform-model/dax-query-view

Microsoft. (2025b). Implement time-based calculations in Power BI. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-time-intelligence

Microsoft. (2025c). Set and use date tables in Power BI Desktop. Microsoft Learn. https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

Microsoft. (2026). CALCULATE function (DAX). Microsoft Learn. https://learn.microsoft.com/en-us/dax/calculate-function-dax

O’Leary, D. E. (1999). REAL-D: A schema for data warehouses. Journal of Information Systems, 13(1), 49–62. https://doi.org/10.2308/jis.1999.13.1.49