Lab 08: Relationships, date table, and measures

At a glance

Time

About 45 minutes

Level

intermediate

Platforms

Windows 11, macOS (file mode, no Desktop)

Copilot surfaces

VS Code, Power BI Desktop

You'll use

custom-agent, prompt-file, skill, hook

Starts from

lab07-complete: Business names, descriptions, hidden keys, display folders, and a rebound report.

Ends at

lab08-complete: Adds relationships, the marked date table, Fill Type, and the core measures.

Last verified

Not yet run end to end on Windows 11

Works on macOS

Nobody has run this lab end to end on Windows 11 yet. The steps follow the current docs for each tool, but menu labels may differ. The verify command at the end is the source of truth, and feedback is welcome.

Why this matters

Business names make a model readable. Relationships and measures make its answers correct. The baseline model repeats category totals because Medication does not filter Prescription Fill, and it leaves time comparisons up to whichever DAX the agent guesses. In this lab you add the semantic structure that business questions need: single-direction relationships, a marked Date table, decoded fill type, and twelve explicit measures.

Concepts

• Single-direction filter: dimensions filter facts, but facts do not filter dimensions. This keeps totals predictable and avoids ambiguous paths.
• Marked Date table: a real Date table with a key date column. Time intelligence measures use it instead of hidden auto date/time tables.
• Auto date/time off: the model should not contain hidden local date tables. One shared Date table controls calendar logic.
• Explicit measure: a named DAX calculation with a description, format string, and display folder. AI tools should use measures instead of implicit sums.
• Decoded column: a readable calculated column, such as Fill Type, that turns raw codes like N and R into business words.

Before you begin

• You finished Lab 07 on branch feature/ai-ready-names.
• The model has business table names: Date, Store, Medication, and Prescription Fill.
• out/assessment.md still exists from Lab 06.
• Close Power BI Desktop before file edits. Reopen it only when a step asks you to check visuals or DAX.

Skipped a lab or want a clean start?

Restore the lab07-complete checkpoint (Business names, descriptions, hidden keys, display folders, and a rebound report). The script refuses to run if you have uncommitted changes, creates a new lab/lab07-complete-... branch, and restores only fabric/ and lab/samples/. Close Power BI Desktop first.

pwsh ./scripts/lab/Restore-LabCheckpoint.ps1 -Checkpoint lab07-complete

It needs the upstream remote from Lab 01, step 3.

Steps

Step 1. Confirm you are starting from the Lab 07 model

Run the Lab 07 checks first. Lab 08 assumes the rename work is complete.

Windows

git branch --show-current
pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 07

macOS

git branch --show-current
pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 07

Stay on feature/ai-ready-names unless your facilitator tells you to branch again. This lab adds the next commit on the same feature branch.

You should see: the current branch is not main, and Lab 07 passes. If it does not, restore lab07-complete with the catch-up command above.

I finished step 1

Step 2. Fix relationships with the prompt file

In Copilot Chat:

1. Open Copilot Chat: Ctrl+Alt+I (macOS: Ctrl+Cmd+I), or View > Chat.
2. Switch to Agent mode.
3. Pick model-improver.
4. Type /fix-relationships-and-date-table and press Enter.
5. When it asks about Desktop, answer that Desktop is closed.
6. Approve validation commands after you read them.

The prompt defines the required relationships and single-direction filters:

.github/prompts/fix-relationships-and-date-table.prompt.md

---
name: fix-relationships-and-date-table
description: Fix the model's relationships and Date table so time intelligence works, including single-direction many-to-one joins, a marked date table, sort-by columns, and auto date/time turned off.
agent: model-improver
---
Fix the relationships and the Date table in the Contoso Pharmacy semantic model. Use the `tmdl-authoring` skill. The model must already use business names (Lab 07).

Target state:

| Relationship | From | To |
|---|---|---|
| Prescription Fill to Date | `'Prescription Fill'[Fill Date]` | `'Date'[Date]` |
| Prescription Fill to Store | `'Prescription Fill'[Store Key]` | `'Store'[Store Key]` |
| Prescription Fill to Medication | `'Prescription Fill'[Medication Key]` | `'Medication'[Medication Key]` |

- All three are many-to-one and single direction. Remove any bidirectional filter.
- The Date relationship uses `joinOnDateBehavior: datePartOnly`.
- Mark `Date` as the date table: `dataCategory: Time` on the table and `isKey` on `Date`.
- Sort `Month` by `Month Number`, `Year Month` by `Year Month Number`, and `Day of Week` by `Day of Week Number`. Hide the number columns.
- Add a `Calendar` hierarchy with levels Year, Year Quarter, Year Month, Date.
- Turn off auto date/time with `annotation __PBI_TimeIntelligenceEnabled = 0` in `model.tmdl`.

Validate with:

```powershell
pwsh ./scripts/Invoke-Validation.ps1 -Only tmdl,bindings
pwsh ./scripts/Test-ModelConventions.ps1 -Rule C07,C08,C09,C10,C11
```

View .github/prompts/fix-relationships-and-date-table.prompt.md on GitHub

The required objects file is what the lab check uses:

rules/required-model-objects.yaml (lines 19-26)

relationships:
  - from: ["Prescription Fill", "Fill Date"]
    to: ["Date", "Date"]
  - from: ["Prescription Fill", "Store Key"]
    to: ["Store", "Store Key"]
  - from: ["Prescription Fill", "Medication Key"]
    to: ["Medication", "Medication Key"]
single_direction_only: true

View rules/required-model-objects.yaml on GitHub

Run the targeted check:

Windows

pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-relationships

macOS

pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-relationships

You should see: three relationships from Prescription Fill to Date, Store, and Medication. Each relationship is many-to-one and filters in one direction, from the dimension to Prescription Fill.

What varies between runs

Copilot's wording, the order it works in, and the exact DAX or TMDL it writes will differ from what you see here and from your neighbor's run. That's expected.

The agent may edit relationships.tmdl directly or use MCP if Desktop is open. The final relationship set must match the prompt.

These must match:

• Prescription Fill relates to Date on Fill Date to Date
• Prescription Fill relates to Store on Store Key
• Prescription Fill relates to Medication on Medication Key
• No relationship is bidirectional

I finished step 2

Step 3. Mark the Date table and turn off auto date/time

Let the same prompt finish the Date table work. If it stopped after relationships, send:

Continue the /fix-relationships-and-date-table work. Mark the Date table, set sort-by columns, add the Calendar hierarchy, hide sort helpers, and turn off auto date/time.

The target state is in the rules file:

rules/required-model-objects.yaml (lines 6-18)

date_table:
  table: Date
  key_column: Date
  data_category: Time
  auto_date_time: false
  sort_by:
    Month: Month Number
    Year Month: Year Month Number
    Day of Week: Day of Week Number
  hierarchy:
    name: Calendar
    levels: [Year, Year Quarter, Year Month, Date]

View rules/required-model-objects.yaml on GitHub

Run the targeted check:

Windows

pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-date-table

macOS

pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-date-table

You should see: PASS L08-date-table. In the TMDL, Date has dataCategory: Time, Date[Date] is the key, Month sorts by Month Number, Year Month sorts by Year Month Number, Day of Week sorts by Day of Week Number, and auto date/time is off in model.tmdl.

I finished step 3

Step 4. Add the core measures and Fill Type

Run the second prompt:

/add-core-measures

The prompt lists the exact measure names, folders, formats, and the Fill Type requirement:

.github/prompts/add-core-measures.prompt.md

---
name: add-core-measures
description: Add the 12 core measures, with these exact names, definitions, format strings, and display folders, to the Prescription Fill table, plus the Fill Type column.
agent: model-improver
---
Add these measures to the `Prescription Fill` table. Use the exact names so the lab checks and DAX tests find them. Each one needs a `///` description, a `formatString`, and the `displayFolder` shown. Use the `tmdl-authoring` skill.

| Display folder | Measure | Definition |
|---|---|---|
| Volume | Prescriptions Filled | Count of fill rows (new and refill) |
| Volume | New Prescriptions | Prescriptions Filled where the fill is new |
| Refills | Refills | Prescriptions Filled where the fill is a refill |
| Refills | Refill Rate | Refills divided by Prescriptions Filled |
| Refills | Refill Rate Index vs Chain | Refill Rate divided by the Refill Rate for all stores in the same period |
| Refills | High Refill Activity Flag | 1 when Refill Rate Index vs Chain is 1.25 or more, otherwise 0 |
| Processing | Avg Processing Time (min) | Average of the processing minutes column. Never a sum. |
| Time Comparison | Prescriptions Filled PM | Prescriptions Filled in the previous month, using the Date table |
| Time Comparison | MoM Change | Prescriptions Filled minus Prescriptions Filled PM |
| Time Comparison | MoM % | MoM Change divided by Prescriptions Filled PM |
| Time Comparison | Prescriptions Filled PY | Prescriptions Filled in the same period last year |
| Time Comparison | YoY % | Change from Prescriptions Filled PY, divided by Prescriptions Filled PY |

Also:

- Add a calculated column `Fill Type` that decodes `Fill Type Code`: `N` is `New`, `R` is `Refill`. Hide `Fill Type Code`.
- Use `DIVIDE` for every ratio. Build measures on other measures rather than repeating logic.
- Formats: whole numbers `#,0`; rates and percentages `0.0%`; the index `0.00`; minutes `0.0`; the flag `0`.
- After the measures exist, rebind report visuals that still count or sum raw columns (see the `pbir-rebinding` skill).

Validate with:

```powershell
pwsh ./scripts/Invoke-Validation.ps1
pwsh ./scripts/Test-ModelConventions.ps1 -Rule C02,C06,C12,C13
```

On Windows with the model open in Desktop, also run `pwsh ./scripts/Invoke-DaxQuestionTests.ps1`.

View .github/prompts/add-core-measures.prompt.md on GitHub

The convention rules require the same twelve measures:

rules/required-model-objects.yaml (lines 28-34)

measures:
  table: Prescription Fill
  required:
    Volume: [Prescriptions Filled, New Prescriptions]
    Refills: [Refills, Refill Rate, Refill Rate Index vs Chain, High Refill Activity Flag]
    Processing: [Avg Processing Time (min)]
    Time Comparison: [Prescriptions Filled PM, MoM Change, MoM %, Prescriptions Filled PY, YoY %]

View rules/required-model-objects.yaml on GitHub

Run the targeted checks:

Windows

pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-measures,L08-fill-type

macOS

pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-measures,L08-fill-type

You should see: all twelve required measures on Prescription Fill, with descriptions, format strings, and display folders. You should also see a visible Fill Type column that returns New for N and Refill for R, while Fill Type Code is hidden.

What varies between runs

Copilot's wording, the order it works in, and the exact DAX or TMDL it writes will differ from what you see here and from your neighbor's run. That's expected.

The DAX can vary as long as the measures pass the rule checks and later DAX tests. The agent should use DIVIDE for ratios and build measures from base measures.

These must match:

• All 12 required measures exist with exact names
• Measures have descriptions, format strings, and display folders
• Fill Type decodes N to New and R to Refill
• Fill Type Code is hidden

I finished step 4

Step 5. Check category totals in Desktop

Windows only

1. Open fabric\ContosoPharmacy.pbip in Power BI Desktop.
2. Select Home > Refresh.
3. Recreate the quick matrix from Lab 02: rows by Medication[Therapeutic Category], month from Date, and [Prescriptions Filled] as the value.
4. Filter to December 2025, or compare November and December if you want to mirror Q02.

This is the visible proof for the Medication relationship. In the baseline, each category could show the same total because Medication did not filter Prescription Fill. After this lab, category totals differ and add up to the month total.

You should see: category rows with different prescription-fill counts, not the same repeated total for every category. The Respiratory category is the largest December 2025 change when compared with November 2025.

No Desktop?

Skip this visual check. The relationship check in step 2 and the DAX tests in step 7 are the deterministic versions of the same idea.

I finished step 5

Step 6. Run lint and report-binding validation

Close Desktop before running file validation, so it does not rewrite the model while you inspect diffs.

Windows

pwsh ./scripts/Invoke-Validation.ps1 -Only tmdl,bindings
pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-lint

macOS

pwsh ./scripts/Invoke-Validation.ps1 -Only tmdl,bindings
pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-lint

The model-improver agent is also told to validate after every step:

.github/agents/model-improver.agent.md (lines 40-48)

## Validate after every step

```powershell
pwsh ./scripts/Invoke-Validation.ps1 -Only tmdl,bindings
pwsh ./scripts/Test-ModelConventions.ps1
```

Fix every lint error before continuing. The repo's post-edit hook also runs the lint and reports errors back to you.

View .github/agents/model-improver.agent.md on GitHub

You should see: PASS tmdl, report bindings pass, and PASS L08-lint. If report bindings fail, ask the improver to rebind visuals that still point at raw columns or implicit aggregations.

I finished step 6

Step 7. Run the DAX question tests in Desktop

Windows only Optional

The DAX test script runs the reference DAX against the model open in Desktop. It is optional because it requires Windows and downloads ADOMD.NET the first time only when you pass -AllowDownload.

scripts/Invoke-DaxQuestionTests.ps1 (lines 1-23)

#Requires -Version 7.2
<#
.SYNOPSIS
    Runs the reference DAX for each business question against the model open in Power BI Desktop
    and checks the results against the expected answers (Windows only).
.DESCRIPTION
    1. Finds the local Analysis Services instance that Power BI Desktop starts for an open model.
    2. Loads ADOMD.NET from .tools/adomd/. With -AllowDownload it downloads the pinned
       Microsoft.AnalysisServices.AdomdClient package from nuget.org first and checks its SHA-512.
    3. Runs each question's reference DAX and grades the rows with the same grader the question
       runs use.
    4. Writes out/dax-tests.json with PASS or FAIL per question. The DAX itself isn't printed.

    The reference DAX uses the business names from Lab 08, so run this after Lab 08 with
    fabric/ContosoPharmacy.pbip open and refreshed.
.PARAMETER Port
    The Desktop Analysis Services port. Default: found from msmdsrv.port.txt (newest instance).
.PARAMETER AllowDownload
    Download ADOMD.NET from nuget.org into .tools/adomd/ if it isn't there yet.
.PARAMETER OutFile
    Where to write results. Default: out/dax-tests.json.
.EXAMPLE
    pwsh ./scripts/Invoke-DaxQuestionTests.ps1 -AllowDownload

View scripts/Invoke-DaxQuestionTests.ps1 on GitHub

scripts/Invoke-DaxQuestionTests.ps1 (lines 35-69)

$AdomdVersion = '19.117.0'
$AdomdSha512 = '85D568E72F5B09A36A0F88497801BAFB1D1E9DF5DA1190ABCF7636C264F78D6BA3978B28A78D2640B338AF0BDD6941A31447E7D33D7E0D9004BA64C96C7F3562'

if (-not $IsWindows) {
    Write-Host 'Skipped: the DAX tests need Power BI Desktop, which runs on Windows only.' -ForegroundColor Yellow
    exit 0
}

$root = Get-RepoRoot
$python = Get-VenvPython -Root $root
if (-not $python) {
    Write-Host 'Run scripts/Initialize-DevEnvironment.ps1 first.' -ForegroundColor Red
    exit 2
}
if (-not $OutFile) { $OutFile = Join-Path $root 'out' 'dax-tests.json' }
$OutFile = [IO.Path]::GetFullPath($OutFile, (Get-Location).Path)

# --- ADOMD.NET ---------------------------------------------------------------------------------
$adomdDir = Join-Path $root '.tools' 'adomd' $AdomdVersion
$libDir = Join-Path $adomdDir 'lib' 'net8.0'
$clientDll = Join-Path $libDir 'Microsoft.AnalysisServices.AdomdClient.dll'
if (-not (Test-Path -LiteralPath $clientDll)) {
    if (-not $AllowDownload) {
        Write-Host "ADOMD.NET $AdomdVersion isn't in .tools/adomd/. Run again with -AllowDownload to fetch it from nuget.org." -ForegroundColor Yellow
        exit 2
    }
    $package = Join-Path $root '.tools' "adomd-$AdomdVersion.nupkg"
    New-Item -ItemType Directory -Force -Path (Split-Path -Parent $package) | Out-Null
    $url = "https://www.nuget.org/api/v2/package/Microsoft.AnalysisServices.AdomdClient/$AdomdVersion"
    Write-Host "Downloading $url"
    Invoke-WebRequest -Uri $url -OutFile $package -UseBasicParsing
    $hash = (Get-FileHash -LiteralPath $package -Algorithm SHA512).Hash
    if ($hash -ne $AdomdSha512) {
        Remove-Item -LiteralPath $package
        throw "The downloaded package hash doesn't match the pinned SHA-512. Nothing was installed."

View scripts/Invoke-DaxQuestionTests.ps1 on GitHub

1. Open fabric\ContosoPharmacy.pbip in Power BI Desktop.
2. Select Home > Refresh.
3. Run the first command with -AllowDownload if .tools/adomd/ is not present.
4. Run it again without the flag.

Windows

pwsh ./scripts/Invoke-DaxQuestionTests.ps1 -AllowDownload
pwsh ./scripts/Invoke-DaxQuestionTests.ps1
pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-dax-tests

If Desktop is running but the script finds the wrong instance, pass the port explicitly:

pwsh ./scripts/Invoke-DaxQuestionTests.ps1 -Port <desktop-port>

macOS

pwsh ./scripts/Invoke-DaxQuestionTests.ps1
pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08 -Only L08-dax-tests

This repo does not currently ship a fabric/ContosoPharmacy.SemanticModel/DAXQueries/ folder. The script gets the reference DAX from the lab tooling and writes pass or fail results without printing the hidden queries.

You should see: on Windows, out/dax-tests.json with all five questions marked PASS, then PASS L08-dax-tests. On macOS, the script prints that DAX tests need Power BI Desktop and skips.

I finished step 7

Step 8. Commit the model improvements

Review the Source Control view (Ctrl+Shift+G, or View > Source Control) before you commit. Expect changes under fabric/ContosoPharmacy.SemanticModel/ and possibly fabric/ContosoPharmacy.Report/ if visuals were rebound.

Windows

git status --short
git add fabric
git commit -m "Add relationships date table and measures"

macOS

git status --short
git add fabric
git commit -m "Add relationships date table and measures"

You should see: one commit on feature/ai-ready-names. out/dax-tests.json and .tools/adomd/ stay untracked or ignored local outputs.

I finished step 8

Verify your work

Run this from the repo root in a PowerShell 7 terminal (Terminal > New Terminal in VS Code):

Windows

pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08

macOS

pwsh ./scripts/lab/Test-LabProgress.ps1 -Lab 08

Checks marked Windows only print SKIP on macOS. SKIP doesn't count as a failure.

When everything is done you should see:

Expected output

PASS  L08-relationships  Three single-direction relationships to Date, Store, and Medication (C07, C08)
PASS  L08-date-table     Date table marked, sort-by columns set, auto date/time off (C09, C10, C11)
PASS  L08-measures       All 12 required measures exist with descriptions, format strings, and display folders (C02, C12, C13)
PASS  L08-fill-type      Fill Type decodes N and R; the raw code is hidden (C06)
PASS  L08-lint           TMDL lint and report bindings pass
PASS  L08-dax-tests      Reference DAX returns the expected answers in Desktop

Lab 08: 6 of 6 checks passed.

Each FAIL line prints a hint and a link back to the step on this page that fixes it. The command exits with code 0 when every required check passes.

Check	What it looks at	If it fails
L08-relationships	Three single-direction relationships to Date, Store, and Medication (C07, C08)	Run /fix-relationships-and-date-table. Go to step 2
L08-date-table	Date table marked, sort-by columns set, auto date/time off (C09, C10, C11)	Run /fix-relationships-and-date-table and check the Date table in Desktop. Go to step 3
L08-measures	All 12 required measures exist with descriptions, format strings, and display folders (C02, C12, C13)	Run /add-core-measures. Go to step 4
L08-fill-type	Fill Type decodes N and R; the raw code is hidden (C06)	Add a Fill Type column and hide Fill Type Code. Go to step 4
L08-lint	TMDL lint and report bindings pass	Run pwsh ./scripts/Invoke-Validation.ps1. Go to step 6
L08-dax-tests (optional) (Windows only)	Reference DAX returns the expected answers in Desktop	Run pwsh ./scripts/Invoke-DaxQuestionTests.ps1 with the model open in Desktop. Go to step 7

Optional checks can FAIL without failing the lab.

Troubleshooting

The relationship check still says Medication is missing

Ask the improver to inspect definition/relationships.tmdl and add the relationship from 'Prescription Fill'[Medication Key] to 'Medication'[Medication Key]. Then rerun pwsh ./scripts/Test-ModelConventions.ps1 -Rule C07,C08.

Date measures return blank

Check that Date is marked as the date table and that the relationship uses Prescription Fill[Fill Date] to Date[Date]. Also confirm auto date/time is off. Time-intelligence measures should use the Date table, not hidden local date tables.

The measure check reports a name mismatch

Use the exact names from /add-core-measures. MoM % is not the same as MoM Percent, and Prescriptions Filled PM is not the same as Previous Month Fills. Rename the measure and rebind any visuals that used the wrong name.

Invoke-DaxQuestionTests.ps1 cannot find Desktop

Open fabric\ContosoPharmacy.pbip, refresh it, and wait until the model is loaded. The script finds the newest msmdsrv.port.txt under the Power BI Desktop Analysis Services workspace folders. If you have multiple Desktop windows open, close the extras or pass -Port <desktop-port>.

The ADOMD.NET download fails

The first run with -AllowDownload downloads from nuget.org into .tools/adomd/ and checks a pinned SHA-512 hash. If your network blocks NuGet, skip the optional DAX test and rely on the TMDL, binding, and convention checks until you can run on a network that allows the package.

Knowledge check

Why should relationship filters flow from Date, Store, and Medication to Prescription Fill only?

Single-direction dimension-to-fact filters keep totals predictable and avoid ambiguous paths.Power BI Desktop cannot save bidirectional relationships.Copilot ignores bidirectional relationships.

Show the answer

Single-direction dimension-to-fact filters keep totals predictable and avoid ambiguous paths.

Dimensions should filter the fact table. Bidirectional filters can make unrelated slicers and totals interact in surprising ways.

Why add explicit measures instead of relying on implicit column aggregation?

Measures encode business logic, names, descriptions, formats, and display folders for people and AI tools.Implicit aggregation is disabled in every Power BI model.Measures are required only for visuals, not for DAX queries.

Show the answer

Measures encode business logic, names, descriptions, formats, and display folders for people and AI tools.

An explicit measure tells the agent exactly how to count fills, compare periods, and calculate rates. Implicit sums often pick the wrong aggregation.

What does the Fill Type column fix?

It decodes raw N and R codes into New and Refill and lets the raw code stay hidden.It creates the Date relationship.It stores the DAX test results.

Show the answer

It decodes raw N and R codes into New and Refill and lets the raw code stay hidden.

Readable decoded columns are easier for analysts and AI tools. The raw code remains available to DAX but hidden from report authors.

Recap and next

You fixed the structural problems that made the baseline answers weak: missing and bidirectional relationships, an unmarked Date table, raw fill-type codes, and missing measures. The model now has the core DAX needed for volume, refills, processing time, and time comparison. Next you add AI-facing metadata so Copilot and Fabric data agents understand how to use those objects.

Next: Lab 09: AI metadata

Go further

• Ask model-reviewer to review the commit before you move on: “Review the Lab 08 changes against main and focus on relationships, Date table settings, and measures.”
• Open the DAX for Refill Rate Index vs Chain and explain why it removes Store filters but keeps the current period.
• Add a simple report visual that uses [High Refill Activity Flag], then run pwsh ./scripts/Test-ReportBindings.ps1.

Something wrong or unclear on this page? Open a lab feedback issue. The form is prefilled with this page's name. Nothing is sent until you submit it, and please don't include screenshots of real accounts.