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
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,
Great to hear the DateTable approach worked! Let me address your performance and refresh configuration concerns:
Performance Optimization for DirectQuery in Power BI Service
Immediate Actions to Improve Performance
- Reduce Visual Count on Each Page: Each visual generates a separate query to your data source, creating cumulative delays. Split complex dashboards into multiple focused report pages.
- Implement Query Reduction: Use the "Apply" button on slicers instead of automatic filtering. This prevents multiple queries while users are still making selections.
- Optimize Your Data Source: Examine the queries sent to the underlying source using Performance Analyzer in Power BI Desktop. Ensure proper indexing on filtered columns in your database.
- Enable Cross-Filtering Optimization: In Power BI Desktop, go to File > Options > Current File > DirectQuery and enable "Reduce number of queries sent by enabling bi-directional cross-filtering".
DirectQuery Refresh Configuration
Understanding Your Current Setup
From your screenshot, you have Incremental Refresh configured, not DirectQuery refresh scheduling. These are different concepts:
- Incremental Refresh: Controls which historical data gets imported vs. kept in DirectQuery mode
- DirectQuery Refresh: Controls how often DirectQuery visuals update in the service
Setting DirectQuery Refresh Intervals
The 4-hour refresh window I mentioned refers to Automatic Page Refresh, not the incremental refresh settings you're showing. Here's how to configure it:
Option 1: Configure in Power BI Desktop
- Open your report in Power BI Desktop
- Go to Modeling tab > Page refresh
- Set refresh interval (minimum depends on your capacity - typically 30 minutes for Premium)
- Publish to service
Option 2: Configure in Power BI Service
- Open your published report in edit mode
- Select any visual
- In the Visualizations pane, look for "Page refresh" settings
- Set refresh interval to match your expected data arrival rate
Recommendation for Your Incremental Refresh
Your current 6-day incremental refresh window is reasonable. Consider these adjustments:
- Keep recent 2-3 days in DirectQuery mode for real-time data
- Import older historical data to reduce query load on your source
- Use your separate date table for all time-based filtering instead of fact table dates
The 40-minute load time suggests either network/gateway issues or inefficient queries hitting your source. Focus on optimizing the source queries and reducing visual complexity first.
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI DeveloperExcellent information!! Thank you once again.
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
