Hybrid Model (Import + DirectQuery) — DataCoverageDefinition not pruning DQ partition when querying

[czzlglg]

Hi everyone,

I'm looking for help with a hybrid table implementation where the DataCoverageDefinition is not preventing DirectQuery queries from firing when the filter context falls entirely within the Import partitions.

Use Case

I'm building a Market Surveillance semantic model for energy trading monitoring. The data is at 15-minute delivery interval granularity, resulting in large fact tables. The goal is to import the most recent data (2025 onwards) for fast performance, and use DirectQuery for historical data (pre-2025) so users can still access it when needed.

Environment

• Power BI Service on PPU workspace (XMLA read/write enabled)
• Data source: Google BigQuery
• Compatibility level: 1603
• Tools used: Power BI Desktop, SSMS, Tabular Editor 2, DAX Studio
• Fact tables: main fact table with ~168M rows imported (2025 onwards), 23 columns (mostly integer FK columns), second fact table with ~6M rows imported (2025 onwards), 15 columns (mostly integer FK columns).

What I Did

1. Built the model in Power BI Desktop with my two fact tables connected to BigQuery, filtered by RangeStart/RangeEnd parameters with proper Date.From() casting for query folding
2. All BigQuery dimension tables set to Dual storage mode
3. Date table created in Power Query (locally generated, Import mode, cannot be Dual because it has no external data source)
4. Configured incremental refresh (1 year archive, 14 days refresh window) on both fact tables
5. Published to PPU workspace, first full refresh completed successfully
6. Connected via SSMS, upgraded compatibility level to 1603
7. Added a DirectQuery policyRange partition via TMSL for historical data (pre-2025)
8. Applied DataCoverageDefinition on the DQ partition
9. Executed Process Recalc

DataCoverageDefinition Expressions I Tried

I attempted several expressions on the DQ partition, all of which failed to prune:

• Direct fact table column with range operator: 'fact_traded_volume'[delivery_date_cet] < DATE(2025, 10, 1)
• YEAR function with IN operator: YEAR('fact_traded_volume'[delivery_date_cet]) IN {2010, 2011, ..., 2024}
• Fact table year column with IN operator: 'fact_traded_volume'[delivery_year] IN {2010, 2011, ..., 2024} (where delivery_year is a column extracted in Power Query that folds to BigQuery)

The Problem

After each attempt (with Process Recalc), I tested in DAX Studio with Server Timings enabled. When querying a date entirely within the Import range (e.g., March 2026), the DQ partition still fires SQL queries to BigQuery. The query takes 65+ seconds instead of the 80-250ms baseline I measured before adding the DQ partition.

The SQL query captured in Server Timings shows BigQuery being queried with a WHERE clause for the recent date, confirming the engine is not skipping the DQ partition despite the DataCoverageDefinition.

Why I Can't Use RELATED() with a Dual Date Table

I understand the Microsoft documentation example uses RELATED('DimDate'[CalendarYear]) IN {2017, 2018, 2019} with DimDate in Dual mode. However, my date table is generated locally in Power Query (using List.Dates) because there is no date dimension table in BigQuery, and I don't have write permissions to create one. A locally generated Power Query table cannot be set to Dual mode because it has no external data source reference, Power BI Desktop rejects it with the error: "DirectQuery partition has 0 datasource references in its expression which is not allowed."

My Questions

1. Has anyone successfully used DataCoverageDefinition with a direct fact table column reference (no RELATED)? The Microsoft documentation lists fact table columns as supported, but I cannot get it to work in practice.
2. Is there a known limitation where DataCoverageDefinition only works with RELATED() and a Dual dimension, despite the documentation suggesting broader support?
3. For the reverse hybrid table pattern (Import for recent data, DirectQuery for historical), has anyone found a working solution that actually prunes the DQ partition when querying only Import data?
4. Is there a workaround for the Dual mode requirement on the date table when the date table is not sourced from the same DirectQuery connection?

Partition Structure (from TMSL script)

json

{
  "name": "Cold_DQ_Pre2025",
  "mode": "directQuery",
  "source": {
    "type": "policyRange",
    "start": "2010-01-01T00:00:00",
    "end": "2025-01-01T00:00:00",
    "granularity": "year"
  },
  "dataCoverageDefinition": {
    "description": "Cold historical data before 2025",
    "expression": "'fact_traded_volume'[delivery_year] IN {2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024}"
  }
}

The Import partitions are auto-managed by incremental refresh (policyRange type, monthly/daily granularity).

Any guidance or shared experience would be greatly appreciated. I've spent considerable time on this and would like to understand whether this is a current limitation of the engine or if I'm missing something in the configuration.

Thanks in advance.

[v-pnaroju-msft]

Hi czzlglg,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @Ritaf1983 for your response.

Hi czzlglg,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solution provided by @Ritaf1983 to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[Ritaf1983 · Accepted solution]

Hi @czzlglg 

The issue stems from a "Cross-island" query limitation. When your Date table is in Import mode and the Fact table contains DirectQuery partitions, the engine cannot guaranteed pruning via DataCoverageDefinition. It defaults to querying the DQ partition to ensure data integrity.

To fix this, the Date table must be set to Dual storage mode. Since you cannot create a physical table in BigQuery, you must use a SQL-based "dummy" source.

The Workaround: "Native Query" Date Table
Power BI requires a data source reference to enable Dual mode. You can bypass the need for a physical table by generating the dates via a BigQuery SQL statement:

Change the Power Query source for your Date table:
Instead of List.Dates, use Value.NativeQuery against your BigQuery connection:

SQL
SELECT * FROM UNNEST(GENERATE_DATE_ARRAY('2010-01-01', '2030-12-31', INTERVAL 1 DAY)) AS Date
Set Storage Mode to Dual: Power BI will now allow this because it sees a valid BigQuery data source.

Update DataCoverageDefinition: Use the RELATED function pointing to the Year column in your now-Dual Date table:

JSON
"dataCoverageDefinition": {
"expression": "RELATED('Date'[Year]) < 2025"
}
Process Recalc: Perform a full "Process Recalc" via SSMS or Tabular Editor to update the metadata.

Answers to Your Questions
Does DataCoverageDefinition work with direct fact columns?
Technically yes, but only if the user applies the filter directly to the fact table column in the report. If they filter via the Date table (standard practice), the pruning will fail unless the Date table is Dual.

Is there a known limitation with RELATED?
Yes. Pruning across Import/DQ islands is unreliable. The engine requires the dimension to exist in the same "island" as the DQ partition, which is what Dual mode achieves.

Workaround for the Dual mode requirement?
As detailed above, use a Native SQL query (GENERATE_DATE_ARRAY) in BigQuery. This satisfies the requirement for an external data source reference without needing write permissions or a physical table in the database.

Recommended TMSL Structure
JSON
{
"name": "Cold_DQ_Pre2025",
"mode": "directQuery",
"source": {
"type": "policyRange",
"start": "2010-01-01T00:00:00",
"end": "2025-01-01T00:00:00",
"granularity": "year"
},
"dataCoverageDefinition": {
"description": "Pruning via Dual Date table",
"expression": "RELATED('Date'[Year]) < 2025"
}
}
By switching to a Dual date table via the SQL-generation method, the engine will be able to evaluate the filter context against the DataCoverageDefinition before generating the SQL for BigQuery, effectively pruning the DQ partition for 2026 queries.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly