Context in DAX formulas
Introduction to DAX in Power BI
Jess Ahmet
Content Developer, DataCamp
Introduction to Context
- Enables dynamic analysis where results of a formula change to reflect the selected data
- There are 3 types of context: row, filter and query
Introduction to Row Context
- "The current row"
Introduction to Row Context
- "The current row"
Calculated Column
- Includes values from all columns within the current row
Introduction to Row Context
- "The current row"
Calculated Column
- Includes values from all columns within the current row
| Item | Price | Tax | Price_with_tax |
|---|---|---|---|
| A | $ 20 | 25% | $25 |
| B | $ 45 | 0% | $45 |
| C | $ 100 | 15% | $115 |
Introduction to Row Context
- "The current row"
Measures
- Can apply when using iterator functions which compute calculations row by row
- Iterator functions can be identified by an
Xafter the function name i.eSUMX() - Syntax:
SUMX(<table>, <expression>)
Introduction to Row Context
- "The current row"
Measures
- Can apply when using iterator functions which compute calculations row by row
- Iterator functions can be identified by an
Xafter the function name i.eSUMX() - Syntax:
SUMX(<table>, <expression>)
| Item | Price | Tax | Price_with_tax |
|---|---|---|---|
| A | $ 20 | 25% | $25 |
| B | $ 45 | 0% | $45 |
Introduction to Row Context
- "The current row"
Measures
- Can apply when using iterator functions which compute calculations row by row
- Iterator functions can be identified by an
Xafter the function name i.eSUMX() - Syntax:
SUMX(<table>, <expression>)
| Item | Price | Tax | Price_w_tax |
|---|---|---|---|
| A | $ 20 | 25% | $25 |
| B | $ 45 | 0% | $45 |
| Total | - | - | $ 70 |
- Example:
SUMX(Sales, Sales[Price] + (Sales[Price] * Sales[Tax]))
Introduction to Filter Context
Filter context is a set of filters that have been applied before the calculation is carried out.
Filter context can be applied in several ways:
- Attributes in a row/column
- Via a slicer
- Through the filter pane
- In a calculated measure
Introduction to Filter Context
Filter context is a set of filters that have been applied before the calculation is carried out.
Example:
| Color | Quantity |
|---|---|
| Blue | 1,250 |
| Green | 200 |
| Black | 4,000 |
Introduction to Filter Context
Filter context is a set of filters that have been applied before the calculation is carried out.
Example:
| Color | Quantity |
|---|---|
| Blue | 1,250 |
Introduction to Filter Context
Filter context is a set of filters that have been applied before the calculation is carried out.
Example:
| Color | Quantity |
|---|---|
| Blue | 1,250 |
| Green | 200 |
| Black | 4,000 |
Introduction to Filter Context
Filter context is a set of filters that have been applied before the calculation is carried out.
Example:
| Socks | Shoes | T-shirt | |
|---|---|---|---|
| Blue | 200 | 800 | 250 |
| Green | 90 | 10 | 100 |
| Black | 2,000 | 800 | 1,200 |
Introduction to Filter Context
Filter context is a set of filters that have been applied before the calculation is carried out.
Example:
| Socks | |
|---|---|
| Blue | 200 |
Calculate Function
- Syntax:
CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])- Expression: a measure or calculation to be evaluated. Must return a single value.
- Filters:
- Filters need to evaluate as a table
- Filters should not clash with one another
Sales[City]="London",Sales[Country] <> "United Kingdom"
CALCULATE()filters will always override filters from the visualization
- Example:
CALCULATE(SUM(Sales), Sales[Region]="EMEA")
Let's practice!
Introduction to DAX in Power BI
