Forum Discussion
Date/Time Columns Lose Formatting with DirectQuery in Incremental Refresh
Hey bIearner83,
Quick Fix for DirectQuery Date Hierarchy Issue
What's Happening
DirectQuery real-time mode bypasses Power Query transformations, so your date columns lose their hierarchy recognition. This is a known limitation.
Best Solutions (Pick One)
Option 1: Separate Date Table (Recommended)
- Create a simple date dimension table in Import mode
- Relate it to your DirectQuery fact table
- Use the date table fields for slicers instead of your fact table dates
- This gives you full hierarchy control and works perfectly with DirectQuery
Option 2: Fix at SQL Source
- In your SQL query, explicitly cast date columns: CAST(InvoiceDate AS DATE) AS InvoiceDate
- Make sure your database columns are proper DATE/DATETIME types
- Sometimes DirectQuery picks up formatting better from the source than Power Query
Option 3: Adjust Refresh Strategy
- Reduce your real-time window (maybe refresh every 2-4 hours instead)
- Keep more data in Import mode, less in DirectQuery
- You might not need real-time for all your historical data anyway
Quick Test
Try Option 1 first - create a basic date table and see if that solves your hierarchy problem. It usually does and doesn't require changing your source data.
Most people find the separate date table approach works best because it's reliable and doesn't depend on DirectQuery's quirky behavior with data types.
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Best regards,
Jainesh Poojara / Power BI Developer
Hi jaineshp thanks so much for your detailed response. I really appreciate it!!
The DateTable works perfectly in my case. However, I am facing an issue where the report is performaning very slow when published to PowerBI service even after 40 minutes.
Additionally, for DirectQuery, where can I set the refresh window to 4 hours? I can only see the below options under Incremental refresh settings. One is for "Archive data" and the second one is for incremental refresh (currently set to 6 days on safe side to not miss any updates)
Hey bIearner83,
Based on your screenshot, I can see you're looking at the incremental refresh settings in Power BI. Let me address both of your concerns:
1. Performance Issues with DirectQuery
The slow performance (40+ minutes) you're experiencing is likely due to DirectQuery's nature - it queries the source database in real-time. Here are some optimization strategies:
Query Optimization:
- Ensure your DateTable logic is as efficient as possible
- Consider adding indexes on date columns in your source database
- Minimize complex calculations in DirectQuery mode
- Use query folding where possible
Alternative Approach:
- Consider switching to Import mode with incremental refresh for better performance
- DirectQuery is best for real-time data needs, but Import mode is typically much faster
2. Setting 4-Hour Refresh Window for DirectQuery
Looking at your screenshot, I notice you have incremental refresh enabled, but DirectQuery doesn't actually use traditional incremental refresh settings the same way Import mode does.
For DirectQuery specifically:
- Real-time updates: DirectQuery automatically gets the latest data on each query - no refresh schedule needed
- Query timeout: You can set query timeout limits in Power BI Service (usually under dataset settings)
- Automatic page refresh: You can set pages to auto-refresh every few hours
Recommended Solution
Given your performance issues, I'd suggest:
- Switch to Import Mode with incremental refresh:
- Keep your current settings (6 days incremental refresh)
- Set up a 4-hour refresh schedule in Power BI Service
- This will give you much better performance
- If you must use DirectQuery:
- Enable "Automatic page refresh" on your reports (set to 4 hours)
- Optimize your queries and database indexes
- Consider using aggregations for summary data
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer