---
applyTo: "**/*.tmdl,**/*.dax,**/*.pbip,**/*.SemanticModel/**,**/*.Report/**"
description: Power BI semantic model and DAX conventions
---

# Power BI and DAX

## Model
- The model is a star schema: fact tables with numeric columns and keys, dimension tables with attributes. Relationships are one-to-many, single direction from dimension to fact. Bi-directional or many-to-many relationships need a comment explaining why.
- One marked date table with continuous dates covers every fact date; relate it on date keys. Disable auto date/time.
- Hide key columns and raw numeric columns that have a measure. Set data types, formats and summarization (`Do not summarize` for IDs and years) on every column.
- Prefer transformations in the source or Power Query over calculated columns; add a calculated column only when it must be sliced or filtered.

## DAX
- Business logic lives in explicit measures stored in a dedicated measures table or display folders. Name measures for what users read ("Sales Amount", "Sales YoY %").
- Use `VAR ... RETURN` for anything used twice or for readability. Use `DIVIDE(n, d)` instead of `/`.
- `CALCULATE` with column predicates (`'Product'[Color] = "Red"`) instead of `FILTER(ALL(Table), ...)` over whole tables. Use `KEEPFILTERS` when a filter should intersect the existing context, `REMOVEFILTERS` to clear it.
- Time intelligence only against the marked date table (`DATESYTD`, `SAMEPERIODLASTYEAR`, `DATEADD`). Inactive relationships via `USERELATIONSHIP` inside the measure.
- Avoid iterating large fact tables with `SUMX`/`FILTER` when a plain aggregation works; avoid `IFERROR`/`ISERROR`.
- Format DAX consistently (one function argument per line for long expressions) and set format strings on measures, not with `FORMAT()`, unless text output is the goal.

## Security and source control
- Row-level security roles filter dimension tables with simple expressions (`[Region] = LOOKUPVALUE(...)` or `USERPRINCIPALNAME()` mapping tables); test each role with "View as".
- Keep projects in PBIP format with TMDL so changes diff cleanly; never commit credentials, cached data or `.pbix` files alongside PBIP sources.