Hi @locka,
You’re very close. The main things slowing you down / blocking a scalar count are:
- using FORMAT(...) in row filters (it forces string scans and kills indexes),
- relying on row-by-row comparisons instead of a single, set-based filter,
- applying disconnected slicers implicitly instead of mapping them with TREATAS.
Below is a drop-in measure that:
1) works against your condensed FACT_FaultEndOfDayAssignment table,
2) supports disconnected date / agent / team slicers, and
3) returns a scalar count (so you can use it in cards, KPIs, or filters).
Active Tickets (range) - counts tickets that overlap the selected date range, filtered by the disconnected slicers:
Active Tickets (range) :=
VAR MinDate = MIN ( '@DIM_Date'[Date] )
VAR MaxDate = MAX ( '@DIM_Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( FACT_FaultEndOfDayAssignment ),
// overlap: [From] <= Max AND COALESCE([To], +infinity) >= Min
KEEPFILTERS (
FILTER (
FACT_FaultEndOfDayAssignment,
FACT_FaultEndOfDayAssignment[FromDatetime] <= MaxDate
&& COALESCE ( FACT_FaultEndOfDayAssignment[ToDatetime], DATE ( 9999, 12, 31 ) ) >= MinDate
)
),
// map disconnected slicers
TREATAS ( VALUES ( '@DIM_Teams'[AgentTeam] ), FACT_FaultEndOfDayAssignment[AgentTeam] ),
TREATAS ( VALUES ( '@DIM_Agent'[AgentID] ), FACT_FaultEndOfDayAssignment[AssignedAgentID] ),
// optional status exclusion (faster than IN/FORMAT)
KEEPFILTERS (
FILTER (
FACT_FaultEndOfDayAssignment,
FACT_FaultEndOfDayAssignment[Status] <> "Rejected"
&& FACT_FaultEndOfDayAssignment[Status] <> "Cancelled"
)
)
)Active Tickets (per day) — same logic, but when you put it on a daily axis it evaluates per the day in context (no strings/FORMAT):
Active Tickets (per day) :=
VAR DayStart = SELECTEDVALUE ( '@DIM_Date'[Date] )
VAR DayEnd = DayStart
RETURN
CALCULATE (
COUNTROWS ( FACT_FaultEndOfDayAssignment ),
KEEPFILTERS (
FILTER (
FACT_FaultEndOfDayAssignment,
FACT_FaultEndOfDayAssignment[FromDatetime] <= DayEnd
&& COALESCE ( FACT_FaultEndOfDayAssignment[ToDatetime], DATE ( 9999, 12, 31 ) ) >= DayStart
)
),
TREATAS ( VALUES ( '@DIM_Teams'[AgentTeam] ), FACT_FaultEndOfDayAssignment[AgentTeam] ),
TREATAS ( VALUES ( '@DIM_Agent'[AgentID] ), FACT_FaultEndOfDayAssignment[AssignedAgentID] ),
KEEPFILTERS (
FILTER (
FACT_FaultEndOfDayAssignment,
FACT_FaultEndOfDayAssignment[Status] <> "Rejected"
&& FACT_FaultEndOfDayAssignment[Status] <> "Cancelled"
)
)
)Notes
- No FORMAT calls (huge perf win).
- TREATAS cleanly applies your disconnected slicers.
- Your Power Query “end-of-day” condensation means each FaultId has at most one row per day, so a COUNTROWS after set filters is both correct and fast.
- Keep your existing CROSSFILTER only if you genuinely need to override a relationship; otherwise let the model relationships do the work.
why this is faster + reliable
- Events-overlap pattern
You want tickets that were “active” at any point in the selected window. The canonical test is: [From] <= WindowMax AND COALESCE([To], +infinity) >= WindowMin. That’s what the FILTER(...FromDatetime <= MaxDate && COALESCE(ToDatetime, 9999-12-31) >= MinDate...) does. See: TREATAS, CALCULATE, FILTER. - Disconnected slicers done right
Because your date/agent/team slicers are disconnected (by design), you must map their selections to the fact table. TREATAS projects the selected values onto the fact columns efficiently, without building large intermediate tables or resorting to SUMMARIZECOLUMNS. Docs: TREATAS. - Avoid FORMAT in filters
FORMAT(DateTime, "Short Date") turns an indexed datetime into a string, defeating storage engine optimizations and forcing row-by-row scans. Using native comparisons on the actual Date/DateTime columns is both more correct (no locale surprises) and much faster. - Keep filters narrow and composable
The KEEPTFILTERS(FILTER(...)) pattern scopes the comparison to the fact table only, letting the rest of your model’s filters flow normally. That’s usually faster than wrapping everything in a wide CALCULATE/FILTER over ALLSELECTED(...). - Totals vs daily
- Place Active Tickets (per day) on a line chart or matrix by Date for daily counts.
- Use Active Tickets (range) in cards/KPIs to get the total distinct active rows across the whole selection window. - If you ever remove the “EOD condensation”
You can still get the “last audit entry per day per FaultId” in DAX with a virtual table, but keep it for Power Query if you can—it’s cheaper to pre-reduce the cardinality once than re-compute it at query time.
Optional tidy-ups
- If you currently use CROSSFILTER(..., Both) in the measure, prefer a proper relationship with the minimal necessary direction. Only keep CROSSFILTER if you’re overriding an inactive/special case.
- If your Date slicer range is large, consider capping the MaxDate to TODAY() (or a data watermark) to prevent accidental “open-ended” windows.
If you’d like, share a tiny PBIX (anonymized) and I can sanity-check the model relationships and confirm you don’t need the CROSSFILTER at all.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
