The problem described by HazmatHill cuts to the core of what happens when a tool is smart enough to compute but not smart enough to know *what* you want computed. Their measure for Estimated Work Hours is perfectly logical: subtract the earliest scan time from the latest, multiply by 24, and you get hours worked. It works at the row level. But when the data collapses into a weekly subtotal, that same logic becomes a liability. It grabs the earliest time across the entire week and the latest time across the entire week, producing a span of five hours instead of the correct sum of 2.26 hours. This isn't a bug. It's a design tension that anyone working with data models eventually hits: measures are evaluated in their filter context, and a collapsed field changes that context entirely.
The practical issue here is that Excel's Data Model treats measures as dynamic calculations, not static values. When you collapse rows, the measure recalculates against the broader scope. That is exactly what it is supposed to do. But what HazmatHill actually needs is a measure that *sums* the daily or per-shift hours, not one that recomputes the span across the entire week. The fix is conceptually simple: create a measure that calculates the hours for each individual row first, then sums those results. In DAX, that might look like `SUMX(Table, ([Latest Time] - [Earliest Time]) * 24)`. The `SUMX` iterator forces the subtraction to happen row by row, and then adds the results. That gives you 2.26 hours for the week, not 5.
This is our opinion: the moment you move from a flat spreadsheet into a data model, you have to stop thinking like a cell reference and start thinking like a filter context. That shift is the hardest part for experienced Excel users, because the muscle memory of "this formula works in this cell" breaks down. The tool is not wrong. It is doing exactly what the measure instructs it to do. The fault is in assuming that a measure that works at one level of detail will automatically work at another. That assumption is the real barrier to adopting smarter tools, and it is the reason many users feel stuck between the familiarity of traditional spreadsheets and the power of AI-native alternatives. The solution is not to fight the tool's logic, but to learn its rules. Once you do, the result is not just a correct subtotal, it is a workflow that scales without manual intervention.