Date/Time Columns Lose Formatting with DirectQuery in Incremental Refresh

[bIearner83]

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!

[jaineshp · Accepted solution]

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

[bIearner83]

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)

[jaineshp]

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:

1. Real-time updates: DirectQuery automatically gets the latest data on each query - no refresh schedule needed
2. Query timeout: You can set query timeout limits in Power BI Service (usually under dataset settings)
3. Automatic page refresh: You can set pages to auto-refresh every few hours

Recommended Solution

Given your performance issues, I'd suggest:

1. 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
2. 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

[jaineshp]

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

[bIearner83]

Excellent information!! Thank you once again.