Power BI: DAX at the Right Granularity

Key insights

• Granularity in DAX: Granularity is the level of detail where a measure runs; you must evaluate a business rule at the lowest grain where every input has one clear meaning.
  Never evaluate the rule above that business-rule grain, because doing so can change results when you aggregate.
• Four-part model: Treat granularity as four linked layers — source-table grain, business-rule grain, iteration/evaluation grain, and requested output grain.
  The table you pass to an iterator (for example, SUMX) fixes the evaluation grain for that calculation.
• Checklist for measure design: Ask which table row defines one input, which keys make inputs unambiguous, and which inputs are additive before the rule runs.
  Confirm the chosen evaluation grain does not depend on fields shown in the visual and that upstream transforms did not remove any required keys.
• Why wrong granularity breaks totals: A measure can show correct detail rows but produce incorrect totals if you evaluated it too high in the hierarchy.
  This mistake often appears with non-additive logic like rates, thresholds, percentages, or conditional rules.
• How to implement correctly: Build a table expression at the business grain, iterate over it (for example with SUMX or VALUES), evaluate the rule per row, then aggregate the row results to the requested level.
  If the report requests detail below the supported grain, return a blank or detect unsupported detail to avoid wrong answers.
• DAX and modeling context: DAX is the formula language for Power BI, Analysis Services, and Power Pivot, and granularity is a core modeling concept in those tools.
  Choose the coarsest grain that still keeps each input unambiguous to balance safety and performance.

Keywords

DAX granularity, granularity in DAX, Power BI DAX best practices, writing DAX measures at correct granularity, DAX aggregation level, DAX row context vs filter context, optimize DAX granularity, DAX measure granularity examples