Forum Discussion
Date/Time Columns Lose Formatting with DirectQuery in Incremental Refresh
Hi all,
I'm encountering an issue in Power BI where my Date/Time columns lose their formatting and hierarchy capabilities when I enable the following setting under Incremental Refresh policy:
✅ “Get the latest data in real time with DirectQuery (Premium Only)”
✅ What works:
When DirectQuery is NOT enabled, Power BI correctly recognizes the columns as datetime, and I can use built-in Date Hierarchy (Year > Month > Day, etc.) in slicers and visuals.
Screenshots:
❌ What breaks:
As soon as I enable DirectQuery for real-time data, the datetime columns appear as plain fields, and the hierarchy is no longer available.
I have explicitly applied the correct types in Power Query, e.g.:
= Table.TransformColumnTypes(#"Removed Columns",{{"CreationTime", type datetime}, {"LastModificationTime", type datetime}, {"EffectiveTime", type datetime}, {"InvoiceDate", type datetime}, {"DueDate", type datetime}, {"ReceivedDate", type datetime}, {"PaymentDate", type datetime}})But this does not resolve the issue once DirectQuery is active for the latest partition. Example screenshots:
What I Need:
I rely on the Date/Time hierarchy in slicers to let users filter data by day, month, and year. Without the hierarchy, the UX is significantly degraded.
Tried So Far:
Applied type datetime conversion in Power Query (both before and after RangeStart / RangeEnd filters)
Verified data types in the SQL source are already correct
Attempted to build manual hierarchies using calculated columns, but that doesn’t work with DirectQuery
Has anyone faced this issue and found a workaround?
Any way to retain Date/Time hierarchy support while using real-time DirectQuery incremental refresh?
Thanks in advance!
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
5 Replies
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 DeveloperHi 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 Developer
