The SQLBI YouTube video examines what it calls the hidden complexity of a daily average in DAX. It begins by showing that a seemingly simple average can return different values depending on visual layout, filters, and missing rows. The presenters demonstrate how relying on a naïve formula may lead to misleading results in reports, framing the problem as one of validation and design rather than a single syntax issue.

SQLBI highlights that the issue often appears when measures are used across cards, matrices, and charts where totals behave unexpectedly. The video emphasizes that understanding filter context and which dates actually exist in a model is critical. The presenters show examples where totals do not equal the sum of row-level averages, stressing the importance of testing across different visual and temporal scenarios before deploying measures to users.

The Technical Challenge

At the core, the video explains that DAX evaluates expressions within an active filter context, which changes results based on the visual or query. For example, a measure that computes a daily metric per row and then averages those rows may ignore days that have no fact rows. As a result, charts and totals can show values that do not match user expectations for a true day-based average. Missing dates, aggregation behavior, and context transitions create a set of edge cases to consider.

The presenters contrast the use of AVERAGE and AVERAGEX and explain when each function can mislead authors. They show that AVERAGEX calculates an expression across a table and that the table definition matters a great deal. For instance, iterating over a fact table will ignore dates without transactions while iterating over a calendar table will include all dates in the period. Consequently, the choice of table for iteration becomes a major design decision for a correct daily average.

SQLBI’s Approach and the Role of AI

SQLBI documents a workflow in which AI is used to draft an initial measure and then the team stress-tests it with edge scenarios. First, the AI suggestion gives a candidate formula that the authors then validate against multiple visual contexts. Next, SQLBI refines the measure to handle missing days and to ensure totals reflect averages of daily values rather than aggregated sums. AI speeds up the prototype phase, but human testing remains essential to reach a reliable result.

The video argues that this hybrid approach changes the authoring process from trial-and-error to an iterative, test-driven exercise. The presenters demonstrate several failing scenarios, adjust the code, and then show corrected behavior in both matrix totals and time-intelligent views. They stress that AI is a helpful assistant rather than a substitute for domain knowledge and recommend using AI to accelerate experimentation while keeping final validation in the hands of the modeler.

Tradeoffs and Practical Guidance

The video also discusses tradeoffs such as accuracy versus complexity and performance versus clarity. For example, forcing a daily average to include all dates can improve semantic correctness but may add computation cost when using iterators over a large calendar. Conversely, simpler formulas can be faster but risk producing misleading totals or omitting empty days. Authors must balance user expectations, model size, and query performance when choosing an approach.

In practice, SQLBI recommends clear choices: use an explicit calendar table, decide whether missing dates should count in denominators, and test measures in every visual type that users will see. They also suggest documenting the behavior of measures so report consumers understand how totals are calculated, and adding unit-style checks that verify measure behavior for typical edge cases to reduce surprises in production reports.

Conclusion and Key Takeaways

The SQLBI video turns a common DAX task into a case study about robustness and testing. It shows that a daily average is not inherently difficult, but it becomes tricky when reports involve missing dates, different visual aggregations, and time-intelligent calculations. The recommended workflow combines AI-assisted drafting with deliberate edge-case validation to produce trustworthy measures, speeding development while reducing the likelihood of reporting errors.

Report authors should adopt an explicit design mindset: choose whether the denominator counts calendar days or transaction days, build measures against a calendar table when appropriate, and verify results across cards, matrices, and charts. Documenting decisions and including simple checks helps teams manage tradeoffs between performance and semantic accuracy. Ultimately, the video provides practical, actionable guidance for anyone who needs daily averages in Power BI or other DAX-based environments.