Table relationships with ambiguous paths treated differently between DQ and Import

Hi Sandip_Palit ,

Why this happend

In DirectQuery, Power BI is more permissive

• Power BI pushes queries directly to the source (Databricks in your case).

• Since the source database is handling the joins, Power BI tolerates ambiguous relationships (like multiple active paths or many-to-many joins) that would otherwise violate Import model constraints.

• It assumes the source engine can optimize or resolve the join ambiguity.

 In Import Mode, Power BI strictly enforces relationship rules. Import mode loads data into Power BI's internal VertiPaq engine, which requires: 

In your diagram:

1. date_range ↔ active_framework and daily_nav → many-to-many via effective_date.

2. active_framework ↔ unique_frameworks → many-to-many via framework.

3. Bidirectional filters in both relationships.

This creates multiple ambiguous paths from date_range to daily_nav via different join paths — acceptable in DQ, rejected in Import.

How to Solve This

Option 1: Remove Ambiguity (recommended for Import mode)

• Create true dimension tables (with unique values for effective_date and framework).

• Use single active path (e.g., from date_range to daily_nav) and disable bidirectional filters.

• If needed, use TREATAS() or USERELATIONSHIP() in DAX to enable relationships selectively in measures.

Option 2: Use Composite Model (DQ + Import hybrid)

• Leave ambiguous tables in DQ mode if necessary (e.g., daily_nav).

• Use Import for lookup tables (unique_frameworks, date_range) with flattened or pre-aggregated values to reduce complexity.

• Be aware that cross-source relationships might require aggregation and not support all DAX patterns.

Option 3: DAX Workaround for Filters

If you still need interaction but can’t use bidirectional filters, use this pattern:CALCULATE(
[Measure], TREATAS(VALUES('Unique Frameworks'[framework]),'Active Framework'[framework]))

This manually propagates filters between non-related tables, mimicking bidirectional filtering without activating it.