Background
In many Power BI reports, the main page is filtered at a period level—for example, a selected month such as February 2025.
When users drill through to a detail page, the requirement often expands to:
- display all daily records for the selected period, and
- at the same time display all daily records for a comparison period
The comparison period can be:
- previous month
- next month
- prior year
- previous quarter
- or any custom offset
The Core Challenge
The drillthrough page receives a filter from DimDate, which defines the selected period.
This creates a fundamental limitation:
-
visuals using
DimDateonly show the selected period - another period cannot be shown simultaneously using the same relationship
👉 A single connected date dimension can only propagate one active filter context.
Initial Approach: Connected DimDate (RolePlay)
A common first attempt is:
-
create a second date table:
DimDate (RolePlay) - connect it to the fact table
- use it for the comparison period
Design
-
DimDate→ drives drillthrough (selected period) -
DimDate (RolePlay)→ drives comparison period
Why This Approach Fails (Performance)
Connecting DimDate (RolePlay) directly to the fact table introduces significant performance overhead.
-
More complex filter propagation
Multiple date relationships force the engine to evaluate more complex filter paths, reducing optimization efficiency. -
Increased formula engine workload
The engine spends more time resolving competing date contexts instead of pushing simple queries to the storage engine. -
Reduced cache reuse
Additional date paths create more unique filter combinations, which limits cache effectiveness. -
Slower queries at scale
For large models with daily granularity and complex measures like[MeasureX], this results in noticeably slower report performance.
👉 In short, multiple connected date tables increase computation cost and reduce performance efficiency.
Final Solution: Disconnected DimDate (RolePlay) + TREATAS
A better design is:
-
keep only one connected
DimDate -
convert
DimDate (RolePlay)into a disconnected helper table -
use
TREATASinside measures to control the date context
Key Idea
👉 Do not rely on relationships — control date context explicitly in the measure.
Implementation Pattern
1. Connected date table
-
DimDateremains the only table connected to the fact table - handles drillthrough and standard filtering
2. Disconnected helper table
-
DimDate (RolePlay)has no relationship - used only to generate comparison-period rows
3. Measure logic
Comparison Period MeasureX =
VAR _RolePlayDate =
SELECTEDVALUE('DimDate (RolePlay)'[Date])
RETURN
CALCULATE(
[MeasureX],
REMOVEFILTERS('DimDate'), -- remove drillthrough filter
TREATAS({_RolePlayDate}, 'DimDate'[Date]) -- apply desired date
)
How It Works
Step 1 — Remove original filter
- clears the drillthrough period (e.g., February)
Step 2 — Apply new date
- injects comparison period date (e.g., January)
Result
- no conflicting filters
- precise control of date context
- correct results for both periods
Benefits
- Better performance → simpler filter paths
- Cleaner model → single source of truth
- More flexible → supports any time offset
- Easier to maintain → explicit logic
Recommended Pattern
-
✅ One connected
DimDate -
✅ One disconnected
DimDate (RolePlay) -
✅ Use
TREATASin[MeasureX] - ❌ Avoid multiple connected date tables
Conclusion
The limitation comes from the fact that a connected date dimension can only support one active period at a time.
Instead of introducing additional connected date tables, the optimal solution is:
- use a disconnected helper table
-
explicitly override date context using
TREATAS
Executive Summary
Use a single connected DimDate, a disconnected helper date table, and override date context in [MeasureX] using REMOVEFILTERS and TREATAS. This avoids performance issues and enables flexible multi-period analysis in drillthrough pages.
The Commercial Dashboard has been revised and published to Production.
As part of the update, I reviewed some of the basic logic, including actual data inclusion, and fixed several logic issues such as Run Rate and Release PBI flags.
Once PBI is updated from RDPlan, the dashboard will be updated accordingly. There are currently no additional WBS items to be mapped, except that we can remove the unused manual mappings.
If needed, we can also review the data line by line and reconcile it against the spreadsheet.
