• You HAVE a Row Context in a Calculated Column.
• But you do NOT have a Row Context in a Measure (Calculated Field).
• A calc column is calculated on a row-by-row basis, so there’s one row
“in play” for each evaluation of the formula.
• So =[Column] resolves to a single value (the value from “this row”),
w/out error.
• “The current row” is called Row Context.
• You may only reference a “naked’ column (naked = no aggregation fxn),
and have it resolve to a single number, date, or text value when you
have a Row Context.
• You HAVE a Filter Context in a Measure / CalcField.
• But you do NOT have a Filter Context in a CalcColumn.
• Each cell in a Pivot’s values area is calculated based on the filters
(coordinates) specified for that cell.
• Those filters resolve to a set of multiple rows in the underlying data
tables, rather than a single row.
• =[Column] is therefore illegal as a formula, or as part of a formula
where a single value is needed.
• So this is why aggregation functions are required in measures – to
“collapse” multiple values into one.
(Slightly) Advanced Concept:
Row Context
(Slightly) Advanced Concept:
Filter Context
Exception: Filter Context in Calc Columns Exception: Row Context in Measures
• Aggregation functions like SUM *always* reference the Filter Context
• Since there is no Filter Context in a calccolumn, =SUM([Column]) will
return the sum of the ENTIRE column – you get the same answer all the
way down.
• But you can tell the DAX engine to use a Row Context as if it were ALSO
a Filter Context, by wrapping the aggregation function in a CALCULATE.
• EX: =CALCULATE(SUM[Column])) “respects” the context of each row,
AND also relationships
• So in a Lookup table, you can use CALCULATE(SUM(Data[Col])) to get
the sum of all “matching” rows from the related Data table.
• Furthermore, the DAX engine always “adds” a CALCULATE “wrapper”
whenever you reference a Measure. So =[MySumMeasure] ALSO
respects Row Context and Relationships.
• Certain functions step through tables one row at a time, even when
used within a Measure.
• Those “iterator” functions are said to create Row Contexts during their
operation.
• Ex: FILTER(
table
,
expr
) and SUMX(
table
,
expr
)
• In both examples, you CAN reference a column, within the
expr
argument, and use that column as a single value, within the
expr
argument.
• Note however that the column MUST “come from” the table specified
in the
table
argument.
• Also note that this Row Context only exists within the evaluation of the
iterator function itself (FILTER, SUMX, etc.) and does NOT exist
elsewhere in the measure formula.
What Makes a Valid Calendar/Dates Table?
1. Must contain a column of actual Date data type, not just text or a number that looks like a date.
2. That Date column must NOT contain times – 12:00 AM is “zero time” and is EXACTLY what you want to see.
3. There CANNOT be “gaps” in the Date column. No skipped dates, even if your business isn’t open on those days.
4. Must be “Marked as Date Table” via button on the Power Pivot window’s ribbon (not applicable in Power BI Desktop).
5. May contain as many other columns as desired. Go nuts
6. Should not contain dates that “precede” your actual data – needless rows DO impact performance.
7. You MUST then use this as a proper Lookup table – don’t use dates from your Data tables on Rows/Columns/Etc.!
8
Need Training? Advice? Or Help with a Project?
Contact Us:
Simple@PowerPivotPro.com