DAX - Context Flashcards
How are DAX formulas and context related?
DAX computes formulas within a:
Row Context
or
Filter Context
Writing long sentence to left justify
What is the default context in which a DAX expression is evaluated?
Row Context
What does Row Context mean?
It means the DAX formula is applied row by row
In what scenario is a Row Context automatically applied?
When creating a calculated column
In any other scenario, you would need to create the Row Context yourself
How do you create a row context within a measure?
-
Use an iterator.
An iterator scans a table row by row and computes an expression during the iteration. -
Example, SUMX
Iterates over a table and computes an expression row by row, then aggregates its result with a SUM -
Same Table
All columns that you need for the iterator must be in the same table
Where might a filter context be applied?
- Current visual
- Filter pane
- By Cross-Filtering Visuals
- Slicers
- In the Calculate formula
- Any other means that filters the data
How does row context affect columns in other tables that have relationships to the current table?
It doesn’t
Row context iterates a table. Columns in different tables are not affected by a row context.
What level does the filter context operate at?
The model level
How does the filter context operating at model level affect other tables?
Because it operates at the model level, the filter context affects:
1. The column it is filtering
2. The table the column belongs to
3. All tables that can be reached through relationships, using whatever cross-filters are set up in the model.
What level does the row context operate at?
The table level
Why can you not create a measure simply with a formula Sales[Quantity] * Sales[Net Price]?
- Because measures do not operate on a row context, therefore when you give a calculation like this it doesn’t know what row to apply the calculation to.
-
What DOES work
Measures function at the aggregate level. So you would need something like SUM(Sales[Quantity]) * SUM(Sales[Net Price])
How do you fix the measure formula Sales[Quantity] * Sales[Net Price] so it doesn’t give an error?
Use an aggregate
- For example, SUM( Sales[Quantity] ) is the sum of the Sales[Quantity] column for all the rows.
- It does not require a current row, because all the rows are aggregated together.
Can an aggregate, like SUM, aggregate an expression? For example,
SUM (Sales[Quantity] * Sales[Net Price])
No
An aggregate can only aggregate values in a column, it cannot aggregate an expression
What is a surprising visualization type that is not computed inside a row context?
A Matrix
- Each cell of a matrix is evaluated in a filter context
- If you think more about what a matrix does, aggregates values, it makes sense that even though it is displayed in rows, it is not evaluated in a row context because each of the values is an aggregate
