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,
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 Developer
Excellent information!! Thank you once again.