Power Bi Dax Fundamentals

A calculated formula created using DAX that is evaluated at query time based on filter context. Measures return dynamic values depending on the filters applied in the report, unlike calculated columns which are computed at refresh time.

Filter context is the set of filters applied to a calculation, generated by slicers, visuals, page-level filters, or row-level security. It determines which rows of each table are visible to a measure or expression during evaluation.

CALCULATE evaluates an expression in a modified filter context. It can add, replace, or remove filters on specified columns, overriding the existing filter context for the calculation.

CALCULATE(, , , ...). The expression is evaluated after applying all listed filter arguments, which can be boolean expressions, table expressions, or KEEPFILTERS modifiers.

CALCULATE returns a scalar value, while CALCULATETABLE returns a table. Both modify filter context, but CALCULATETABLE is used when the result is a table of rows needed for further iteration or filtering.

Time intelligence refers to DAX functions that manipulate dates and create period comparisons, such as SAMEPERIODLASTYEAR, TOTALYTD, DATEADD, and PARALLELPERIOD, requiring a marked date table.

A date table must be marked as a date table so DAX time intelligence functions recognize its column as a continuous date range. Without marking, functions like TOTALYTD may produce incorrect results.

TOTALYTD calculates a year-to-date value of an expression from the beginning of the calendar year up to the latest date in the filter context. Syntax: TOTALYTD(, , [], []).

SAMEPERIODLASTYEAR returns a table of dates shifted one year back from the dates in the current filter context, enabling year-over-year comparisons of measures like sales or revenue.

DATEADD shifts a date column by a specified number of intervals (days, months, quarters, years). Syntax: DATEADD(, , ). It is commonly used for flexible period comparisons.

Relationships define how tables connect via shared columns. Power BI supports one-to-many, many-to-one, and one-to-one relationships, controlling how filters propagate across the model.

Filter propagation means filters applied to one table automatically flow to related tables through active relationships. In a one-to-many relationship, filters flow from the 'one' side to the 'many' side.