菜单
Comprehensive guide to designing, naming, and implementing Power BI DAX measures across all business domains. Includes patterns for base aggregations, time intelligence, KPIs, semi-additive measures, and TMDL syntax for measure definitions in .tmdl files.
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
基于 SOC 职业分类
| name | measures |
| description | Comprehensive guide to designing, naming, and implementing Power BI DAX measures across all business domains. Includes patterns for base aggregations, time intelligence, KPIs, semi-additive measures, and TMDL syntax for measure definitions in .tmdl files. |
| version | 2 |
This guide teaches how to scaffold a complete measure layer for any Power BI semantic model. Use this when a user describes their reporting needs to generate production-ready DAX expressions with proper naming, folder structure, and TMDL syntax.
All measures use prefixes to signal their type.
| Prefix | Type | Example | formatString |
|---|---|---|---|
# | Count / Distinct Count | # Orders, # Customers | #,0 |
$ | Currency | $ Revenue, $ Cost | "$#,0.00" |
% | Ratio / Percentage | % Margin, % Growth | "0.00%" |
Δ | Variance (Absolute) | Δ Revenue, Δ Headcount | "#,0" or "$#,0" |
Δ% | Variance (Percent) | Δ% Revenue, Δ% Target | "0.00%" |
| (none) | Helper / Dimension | Days in Period, Current Month Num | varies |
$ Revenue Net of Discounts beats $ Rev ND$ Revenue, $ Revenue PY, $ Revenue YTD$ PY Revenue, $ YTD Revenue├── Key Metrics
│ ├── Revenue & Profitability
│ ├── Efficiency
│ └── Quality
├── Time Intelligence
│ ├── Year-to-Date
│ ├── Prior Year Comparisons
│ └── Period-over-Period
├── Budget vs Actual
│ ├── Actual Measures
│ ├── Budget Measures
│ └── Variance
├── Ratios & Rates
└── Drilling & Details
└── Helper Calculations
Rules:
Variance Analysis folderKey Metrics, not SUM Measures)All generated TMDL uses createOrReplace targeting _measures.
Measures sit at 2 TABs, properties at 3 TABs, multi-line DAX body at 4 TABs:
createOrReplace
ref table _measures
/// Description of the measure
measure '$ Revenue' = SUM(Sales[Revenue])
formatString: "$#,0.00"
displayFolder: "Key Metrics\Revenue & Profitability"
The createOrReplace / ref table _measures header is omitted from the pattern
examples below for brevity — always include it in generated output.
DAX:
$ Revenue = SUM(Sales[Revenue])
# Orders = COUNTROWS(Sales)
TMDL:
/// Total revenue from all sales transactions
measure '$ Revenue' = SUM(Sales[Revenue])
formatString: "$#,0.00"
displayFolder: "Key Metrics\Revenue & Profitability"
/// Number of sales orders
measure '# Orders' = COUNTROWS(Sales)
formatString: "#,0"
displayFolder: "Key Metrics"
DAX:
$ Revenue = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
TMDL:
measure '$ Revenue' =
SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
formatString: "$#,0.00"
displayFolder: "Key Metrics\Revenue & Profitability"
DAX:
# High-Value Orders =
CALCULATE(
COUNTROWS(Sales),
Sales[Revenue] > 1000
)
TMDL:
measure '# High-Value Orders' =
CALCULATE(
COUNTROWS(Sales),
Sales[Revenue] > 1000
)
formatString: "#,0"
displayFolder: "Key Metrics"
DAX:
# Customers = DISTINCTCOUNT(Sales[CustomerID])
TMDL:
measure '# Customers' = DISTINCTCOUNT(Sales[CustomerID])
formatString: "#,0"
displayFolder: "Key Metrics"
DAX:
$ Average Order Value =
DIVIDE(
SUM(Sales[Revenue]),
DISTINCTCOUNT(Sales[OrderID]),
0
)
TMDL:
measure '$ Average Order Value' =
DIVIDE(
SUM(Sales[Revenue]),
DISTINCTCOUNT(Sales[OrderID]),
0
)
formatString: "$#,0.00"
displayFolder: "Key Metrics\Efficiency"
Requires a marked Date table with a
[Date]column and an active relationship to fact tables.
DAX:
$ Revenue YTD =
CALCULATE(
[$ Revenue],
DATESYTD(Date[Date])
)
TMDL:
measure '$ Revenue YTD' =
CALCULATE(
[$ Revenue],
DATESYTD(Date[Date])
)
formatString: "$#,0.00"
displayFolder: "Time Intelligence\Year-to-Date"
DAX:
$ Revenue QTD =
CALCULATE(
[$ Revenue],
DATESQTD(Date[Date])
)
TMDL:
measure '$ Revenue QTD' =
CALCULATE(
[$ Revenue],
DATESQTD(Date[Date])
)
formatString: "$#,0.00"
displayFolder: "Time Intelligence\Quarter-to-Date"
DAX:
$ Revenue MTD =
CALCULATE(
[$ Revenue],
DATESMTD(Date[Date])
)
TMDL:
measure '$ Revenue MTD' =
CALCULATE(
[$ Revenue],
DATESMTD(Date[Date])
)
formatString: "$#,0.00"
displayFolder: "Time Intelligence\Month-to-Date"
DAX:
$ Revenue PY =
CALCULATE(
[$ Revenue],
SAMEPERIODLASTYEAR(Date[Date])
)
TMDL:
measure '$ Revenue PY' =
CALCULATE(
[$ Revenue],
SAMEPERIODLASTYEAR(Date[Date])
)
formatString: "$#,0.00"
displayFolder: "Time Intelligence\Prior Year Comparisons"
DAX:
$ Revenue PY YTD =
CALCULATE(
[$ Revenue],
SAMEPERIODLASTYEAR(DATESYTD(Date[Date]))
)
TMDL:
measure '$ Revenue PY YTD' =
CALCULATE(
[$ Revenue],
SAMEPERIODLASTYEAR(DATESYTD(Date[Date]))
)
formatString: "$#,0.00"
displayFolder: "Time Intelligence\Prior Year Comparisons"
DAX:
$ Revenue L12M =
CALCULATE(
[$ Revenue],
DATESINPERIOD(Date[Date], LASTDATE(Date[Date]), -12, MONTH)
)
TMDL:
measure '$ Revenue L12M' =
CALCULATE(
[$ Revenue],
DATESINPERIOD(Date[Date], LASTDATE(Date[Date]), -12, MONTH)
)
formatString: "$#,0.00"
displayFolder: "Time Intelligence\Period-over-Period"
Use
LASTDATE(Date[Date])notTODAY()— LASTDATE respects the visual's date filter.
DAX:
Δ Revenue vs Target = [$ Revenue] - [Target Revenue]
TMDL:
measure 'Δ Revenue vs Target' = [$ Revenue] - [Target Revenue]
formatString: "$#,0.00"
displayFolder: "Budget vs Actual\Variance"
DAX:
Δ% Revenue vs Target =
DIVIDE(
[$ Revenue] - [Target Revenue],
[Target Revenue],
0
)
TMDL:
measure 'Δ% Revenue vs Target' =
DIVIDE(
[$ Revenue] - [Target Revenue],
[Target Revenue],
0
)
formatString: "0.00%"
displayFolder: "Budget vs Actual\Variance"
Returns 1 (above target), 0 (no data), −1 (below target) — drives conditional formatting or SVG icons.
DAX:
Status Revenue =
IF(
ISBLANK([$ Revenue]),
0,
IF([$ Revenue] >= [Target Revenue], 1, -1)
)
TMDL:
measure 'Status Revenue' =
IF(
ISBLANK([$ Revenue]),
0,
IF([$ Revenue] >= [Target Revenue], 1, -1)
)
formatString: "0"
displayFolder: "Budget vs Actual"
DAX:
Target Revenue = SUM(Budget[TargetRevenue])
TMDL:
measure 'Target Revenue' = SUM(Budget[TargetRevenue])
formatString: "$#,0.00"
displayFolder: "Budget vs Actual\Budget Measures"
Semi-additive measures must not be summed across time. Use LASTNONBLANK or LASTDATE.
Use case: Headcount, inventory, account balance at end of period.
DAX:
# Headcount EOP =
CALCULATE(
SUM(Headcount[Employees]),
LASTNONBLANK(Date[Date], CALCULATE(SUM(Headcount[Employees])))
)
TMDL:
measure '# Headcount EOP' =
CALCULATE(
SUM(Headcount[Employees]),
LASTNONBLANK(Date[Date], CALCULATE(SUM(Headcount[Employees])))
)
formatString: "#,0"
displayFolder: "Key Metrics"
DAX:
$ Account Balance EOD =
CALCULATE(
SUM(GL[Balance]),
LASTDATE(Date[Date])
)
TMDL:
measure '$ Account Balance EOD' =
CALCULATE(
SUM(GL[Balance]),
LASTDATE(Date[Date])
)
formatString: "$#,0.00"
displayFolder: "Key Metrics"
DAX:
$ Average Daily Balance =
AVERAGEX(
VALUES(Date[Date]),
[$ Account Balance EOD]
)
TMDL:
measure '$ Average Daily Balance' =
AVERAGEX(
VALUES(Date[Date]),
[$ Account Balance EOD]
)
formatString: "$#,0.00"
displayFolder: "Key Metrics"
/)Always use DIVIDE(numerator, denominator, alternateResult) — returns alternateResult (default BLANK()) instead of an error when denominator is zero.
% Margin =
DIVIDE(
SUM(Sales[Profit]),
SUM(Sales[Revenue]),
0
)
Catches any error in an expression:
% Utilization =
IFERROR(
DIVIDE(SUM(Hours[Billable]), SUM(Hours[Total]), BLANK()),
BLANK()
)
Prevents downstream errors when a dependency measure is blank:
$ Revenue vs Target =
VAR _actual = [$ Revenue]
VAR _target = [Target Revenue]
RETURN
IF(
ISBLANK(_target),
BLANK(),
_actual - _target
)
| Pattern | Displays as | Use for |
|---|---|---|
"$#,0.00" | $1,234.56 | USD currency |
"€#,0.00" | €1,234.56 | EUR currency |
"#,0" | 1,235 | Whole number counts |
"0.0%" | 12.3% | Percentages (1 decimal) |
"0.00%" | 12.35% | Percentages (2 decimal) |
"#,0,,"M" | 1.2M | Compact millions |
"#,0,"K" | 1.2K | Compact thousands |
When a user describes reporting needs, follow this process:
Example: "Track quarterly sales with prior-year comparison and variance to quota."
Step 1 — Extract metrics:
$ Revenue — base sum$ Revenue PY — prior year comparisonTarget Quota — budget/targetΔ Revenue vs Quota — absolute varianceΔ% Revenue vs Quota — percent varianceStep 2 — Assign folders:
Key Metrics\Revenue & Profitability → $ Revenue
Budget vs Actual\Budget Measures → Target Quota
Budget vs Actual\Variance → Δ Revenue vs Quota, Δ% Revenue vs Quota
Time Intelligence\Prior Year → $ Revenue PY
Step 3 — Generate TMDL:
createOrReplace
ref table _measures
measure '$ Revenue' = SUM(Sales[Amount])
formatString: "$#,0.00"
displayFolder: "Key Metrics\Revenue & Profitability"
measure 'Target Quota' = SUM(Budget[QuotaAmount])
formatString: "$#,0.00"
displayFolder: "Budget vs Actual\Budget Measures"
measure '$ Revenue PY' =
CALCULATE(
[$ Revenue],
SAMEPERIODLASTYEAR(Date[Date])
)
formatString: "$#,0.00"
displayFolder: "Time Intelligence\Prior Year Comparisons"
measure 'Δ Revenue vs Quota' = [$ Revenue] - [Target Quota]
formatString: "$#,0.00"
displayFolder: "Budget vs Actual\Variance"
measure 'Δ% Revenue vs Quota' =
DIVIDE([Δ Revenue vs Quota], [Target Quota], 0)
formatString: "0.00%"
displayFolder: "Budget vs Actual\Variance"
| Property | Purpose | Example |
|---|---|---|
formatString | Display format in visuals | "$#,0.00", "0.00%", "#,0" |
displayFolder | Group in Field List | "Key Metrics\Revenue" |
description | Tooltip in Field List | "Cumulative revenue YTD" |
isHidden | Hide from Field List | isHidden (for helper measures) |
Version: 2.0 Last Updated: 2026-05-02
Icon system for Power BI design. Fluent line style — 20px viewBox, 1.5px stroke, currentColor. Covers naming conventions, sizing grid, semantic color application, inline SVG embedding in DAX measures, full asset index, and accessibility requirements.
Power BI design system for building consistent, accessible, professional reports. Covers the 3-30-300 Rule (visual hierarchy), detail gradient layouts, KPI design principles, anti-patterns ("Power BI Slop"), typography, color palettes, spacing, themes, and evaluation checklists. Emphasizes intentional pre-attentive attributes, semantic colors, and minimizing cognitive load.
Wordmark usage rules and SVG assets for the Contoso placeholder brand. Covers light and dark variants, clear space rules, minimum size, and how to embed in Power BI report headers.
Reference for Power BI Calculation Groups in TMDL. Use this whenever a user asks for time intelligence patterns (YTD, QTD, rolling periods, prior year), scenario switching (Actual/Budget/Forecast), currency conversion, or any pattern where the same transformation needs to apply dynamically across many measures. Always consult this before writing individual YTD/PY/Rolling measures from scratch — a calc group is the correct architecture and eliminates boilerplate at scale.
Reference for DAX User-Defined Functions (UDFs) in Power BI semantic models. Use this when building reusable function definitions with type safety and parameter modes (Val/Expr/AnyRef). Always consult before writing repeated DAX logic that could be abstracted into a single function — UDFs eliminate boilerplate and make complex patterns like time intelligence, statistical functions, and table filters reusable across the entire model.
TMDL syntax ground rules for Power BI semantic models. Use this whenever writing or reviewing TMDL for tables, columns, measures, DAX UDFs, or calculation groups. Always consult before generating any TMDL — these rules govern indentation, object declaration, property syntax, and measure formatting. For UDF type system and examples see dax-udf/SKILL.md. For calculation group patterns see calc-groups/SKILL.md.