A data analyst is building a Power BI model for a subscription service to track active subscriptions. The 'Subscriptions' table contains 'SubscriptionID', 'StartDate', and 'EndDate' columns. The analyst needs to calculate the number of active subscriptions at the end of each month. Which DAX function is most appropriate for a measure that counts active subscriptions at a specific point in time?
- ACALCULATE (DISTINCTCOUNT (Subscriptions[SubscriptionID]), Subscriptions[StartDate] <= MAX('Date'[Date]), Subscriptions[EndDate] >= MAX('Date'[Date]))
- BCALCULATE (COUNTROWS (Subscriptions), FILTER (ALL (Subscriptions), Subscriptions[StartDate] <= MAX('Date'[Date]) && Subscriptions[EndDate] >= MAX('Date'[Date])))
- CCOUNTROWS (FILTER (Subscriptions, Subscriptions[StartDate] <= MAX('Date'[Date]) && Subscriptions[EndDate] >= MAX('Date'[Date])))
- DCOUNTX (Subscriptions, IF (Subscriptions[StartDate] <= MAX('Date'[Date]) && Subscriptions[EndDate] >= MAX('Date'[Date]), 1, 0))
Show answer & explanationAnswer & explanation
Correct answer: B. CALCULATE (COUNTROWS (Subscriptions), FILTER (ALL (Subscriptions), Subscriptions[StartDate] <= MAX('Date'[Date]) && Subscriptions[EndDate] >= MAX('Date'[Date])))
The correct approach for a snapshot measure like 'active subscriptions at month-end' involves iterating over the entire 'Subscriptions' table (using ALL or ALLEXCEPT if specific filters need to be preserved) and applying the date conditions within a FILTER function. CALCULATE then evaluates the COUNTROWS under this modified filter context. This pattern ensures that all subscriptions are considered, regardless of initial filter context, and then filtered based on the date criteria.
Why the other options are wrong
- A. Directly applying filters within CALCULATE like this without a FILTER function on the base table (or ALL/ALLEXCEPT) won't work as intended for row-level evaluation against a date context from another table.
- C. COUNTROWS with FILTER without CALCULATE or ALL might be affected by initial filters on 'Subscriptions' table.
- D. COUNTX iterates row by row and then sums, which is less efficient than filtering first and then counting rows with COUNTROWS for this scenario.
Snapshot Measures (Period-End)
Measures that calculate a value as it stood at a specific point in time, often the end of a period (e.g., month, quarter, year).
- Requires careful handling of filter context, often with ALL or ALLEXCEPT.
- Commonly used for inventory, headcounts, or active subscriptions.
- Involves comparing start/end dates to a 'snapshot date'.
Memory trick: Snapshots freeze the moment, filtering by time.