Advocate IRelationship Configurations: Save & Switch Between Multiple Relationship Sets for the Same Dataset
The Problem: The "One Active Relationship" Constraint Currently, Power BI enforces a strict rule: only one relationship can be active between two tables at a once. In complex models (like Fleet Management, HR, or Finance), we often have multiple dates for a single transaction (e.g., Order Date, Ship Date, Delivery Date, and Payment Date).
To report on these, we are currently forced to:
Duplicate Dimensions: Create "Role-Playing Dimensions," which bloats the model size and confuses end-users.
DAX Measure Bloat: Use USERELATIONSHIP to manually activate a path. If I have 10 core metrics and 4 date paths, I have to write 40 measures. This is a maintenance nightmare and makes the field list impossible to navigate.
The Proposal: Relationship Profiles I propose a new feature called "Relationship Profiles." Within the Model View, a developer could define and name a "Profile" that specifies which relationships are active for that specific context.
How it would work:
In the Model View: A new manager to save "Relationship Sets."
In the Report View: At the Visual, Page, or Report level, a new property in the Format Pane would allow us to select a "Relationship Profile."
The Engine's Logic: When a profile is selected, the engine treats those specific relationships as the "Active" paths for all calculations within that visual’s scope.
Real-World Application (Fleet Management): Imagine a Fleet manager wanting to see "Total Expenses." One chart needs to show expenses by Service Date, another by Payment Date, and a third by Disposal Date. Instead of 3 different DAX measures, they would use one single measure and simply toggle the "Relationship Profile" for each visual.
The Current "Pain Point" (DAX Example)
"
Total_Expenses_by_Service_Date =
// Purpose: Forces the engine to use the 'Service Date' relationship instead of the default 'Transaction Date'.
// Logic: I'm using CALCULATE to override the default active relationship.
// The USERELATIONSHIP function is explicitly telling the engine which path to take between the Fleet table and the Calendar table.
CALCULATE(
SUM( 'Fleet_Expenses'[Amount] ),
USERELATIONSHIP( 'Fleet_Expenses'[Service_Date], 'Date_Table'[Date] )
)
"
This is the repetitive code we have to write today for every single metric in our model. My proposal would make this redundant!
The Value to Microsoft and the Community:
Model Efficiency: Drastically reduces the number of measures required in a model.
Simplified UX: Clean field lists make it easier for "Citizen Developers" to build reports.
Performance: Potentially allows the engine to optimize paths based on predefined profiles rather than dynamic DAX overrides.
What do you guys think? Is it time to move past the "one active relationship" limitation?
