<!-- https://learnsql.cpetoday.com/analyze/fundamentals -->
# DAX fundamentals

Data Analysis Expressions (DAX) is the formula language of Power BI, Power Pivot in Excel and Analysis Services tabular models. It is a functional language: every formula is an expression that returns a value or a table. Most DAX confusion comes down to one idea, evaluation context, so learn that first.

## What DAX is for: measures, calculated columns, calculated tables
- **Measure**: a formula evaluated at query time, in the filter context of wherever it is placed (a card, a matrix cell, a chart bar). It stores no data, so it is the right default for anything you aggregate: `Total Sales = SUM(Sales[SalesAmount])`.
- **Calculated column**: evaluated row by row when the model refreshes and stored in the table, using row context. Use it when the result is a property of the row you will filter, group or slice by (for example `Sales[Margin] = Sales[SalesAmount] - Sales[TotalCost]` or a Customer age band). It costs memory, so do not use a calculated column for something a measure can do.
- **Calculated table**: a table produced by a DAX table expression, computed at refresh (`Calendar = CALENDAR(DATE(2016,1,1), DATE(2018,12,31))`). Available in Power BI Desktop and Analysis Services; Power Pivot in Excel has no New Table command (see the Power Pivot topic for workarounds).
- Rule of thumb: aggregate with a measure; classify or filter by with a column; build lookup/helper tables as tables.

## Row context and filter context
- **Filter context** is the set of filters currently applied to the model: slicers, rows and columns of a matrix, visual and page filters, and filters added by CALCULATE. Aggregators such as SUM, COUNTROWS and DISTINCTCOUNT only see the rows that survive the filter context. This is why one measure shows a different number in every cell.
- **Row context** means "the current row of one table". Calculated columns have it automatically; iterator functions (SUMX, AVERAGEX, FILTER, ADDCOLUMNS ...) create it while they loop. Inside row context you can refer to a column of the current row, as in `Sales[Quantity] * Sales[UnitPrice]`.
- Row context does NOT filter other tables, and by itself it does not filter the same table's aggregations. To turn a row context into a filter, wrap the expression in CALCULATE (this conversion is called context transition). That is why a measure inside an iterator, such as `SUMX(Customer, [Total Sales])`, returns each customer's sales.
- Filters flow along relationships from the one side to the many side (dimension to fact). A slicer on Product therefore filters Sales, but a filter on Sales does not filter Product unless the relationship is bidirectional.

> Diagram: Row context: a calculated column reads one row at a time. Filter context: a measure sums only the rows the filters allow, here the rows where Channel equals Online.

## Implicit versus explicit measures
Dragging a numeric column into a visual makes an implicit measure (a Sum of that column). Prefer explicit measures: they are reusable in other measures, can be formatted once, and show up in the DAX you can read. Hide the raw numeric columns and set `discourageImplicitMeasures` on the model if you want to force it.

## Syntax essentials
- Column reference: `Table[Column]`. Quote table names that contain spaces or special characters: `'Order Details'[Quantity]`. Measure reference: `[Measure Name]` with no table. Always qualify columns with their table and never qualify measures, so you can tell them apart at a glance.
- Operators: `+ - * /`, `^`, comparison `= <> < > <= >=`, text concatenation `&`, logical `&&` (and) `||` (or) `NOT()`, `IN {"A","B"}` and `IN VALUES(Table[Col])`.
- Text is in double quotes, a double quote inside text is doubled `""`. Dates are `DATE(2018,12,31)` or the typed literal `DATE`; do not compare dates with text. Boolean values are TRUE() and FALSE().
- Comments: `// line` , `-- line`, `/* block */`. Indentation and line breaks are free; the DAX formatter (daxformatter.com) or Format Query in DAX query view makes them readable.
- `BLANK()` is not zero and not an empty string. A BLANK in a measure makes that row disappear from many visuals. `COALESCE(x, 0)` turns blank into 0 when you want a number to show.

## Variables: VAR and RETURN
```
Margin % =
VAR Sales = [Total Sales]
VAR Cost = [Total Cost]
RETURN
    DIVIDE(Sales - Cost, Sales)
```
Variables are evaluated once, in the context where they are defined (not where they are used), can hold a scalar or a table, and make formulas faster and easier to read. A variable cannot be changed after it is set, and it is evaluated before CALCULATE changes the context, a frequent source of surprise (and the reason the prior-year pattern below works).

## DIVIDE, BLANK and error handling
Use `DIVIDE(numerator, denominator, [alternate])` instead of `/`: it returns BLANK (or the alternate) when the denominator is zero or blank.

> **Tip.** Use `IFERROR` sparingly because it hides real mistakes and is slower; prefer testing the condition (`IF(ISBLANK(x), ...)`, `DIVIDE`).

`ISBLANK`, `ISEMPTY`, `HASONEVALUE`, `SELECTEDVALUE(Column, [alternate])` are the main tests for what the context holds.

## Data types and formatting
Whole number, Decimal (floating point), Fixed decimal (currency, 4 decimals), Text, Date/time, True/False. Floating-point math can leave tiny remainders; wrap comparisons in ROUND when equality matters. Format with the Format string of the measure (Measure tools or the model's formatString), not with the FORMAT function, because FORMAT returns text and breaks sorting and charts. Use FORMAT only for labels such as dynamic titles.

## A first set of measures for a sales model
```
Total Sales = SUM(Sales[SalesAmount])
Total Cost = SUM(Sales[TotalCost])
Gross Margin = [Total Sales] - [Total Cost]
Gross Margin % = DIVIDE([Gross Margin], [Total Sales])
Units Sold = SUM(Sales[SalesQuantity])
Order Lines = COUNTROWS(Sales)
Customers = DISTINCTCOUNT(Sales[CustomerKey])
Average Price = DIVIDE([Total Sales], [Units Sold])
```
Build every later measure on top of these so a business rule lives in one place.
