Power BI Report not sending correct query to backend

[vegasde100]

I have a strange issue at hand. I have a report that's built off a fact table that's supported by direct query connected to my sql warehouse in databricks. Additionally, I have a number of different date tables that are feeding in data from that same warehouse and connected to my fact table through an import via dataflow. 

Issue I'm running into is when I'm filtering data of transactions say for a single day, the report gets thrown an error saying I've hit a 1M row error, which should be impossible because running this same query manually in the db reveals I should receive no more than 500 rows. I took a look at the query that was pinging the db from the reports, and the report filter for date wasn't being included, although the other filter parameters are being included. 

[jaineshp]

Hey @vegasde100,

This is a classic DirectQuery + Import mixed model issue. Here's what's likely happening and how to fix it:

Root Cause: Your date filter isn't being pushed down to the DirectQuery fact table because Power BI can't bridge filters between Import (date tables) and DirectQuery (fact table) modes in the same model.

Quick Fixes:

1. Check relationship directions - Ensure your date table relationships are set to filter the fact table (single direction from date to fact, or bidirectional if needed)
2. Switch date tables to DirectQuery - Change your date dimension tables from Import to DirectQuery mode so they're in the same storage mode as your fact table
3. Use composite model properly - If you need Import mode for date tables, set up dual storage mode on the date columns that participate in relationships
4. Verify filter context - Check if your date filter is actually being applied at the visual level vs the page/report level
5. DAX measure approach - As a workaround, create measures that explicitly filter by date using DAX instead of relying on relationship filtering

The 1M row limit suggests the query is pulling the entire fact table without the date filter being applied. Start with #2 (switching date tables to DirectQuery) - that's usually the fastest solution for this scenario.

Worth checking your relationship cardinality settings too - sometimes incorrect many-to-many relationships can cause filter propagation issues.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[vegasde100]

Appreciate the explanation, Jainesh. I'll look into changing the date tables from Import to DirectQuery mode. 

One thing that still puzzles me is that, initially what I attempted to do was switch the date slicer field reference from the date table to the date field in the fact table, which still seems to have thrown the 1M row error; any idea on that one?