How to Switch a Table from DirectQuery to Dual Mode in Power BI Desktop

1. Purpose

This article explains the step-by-step process to switch a table’s storage mode from DirectQuery (DQ) to Dual mode in Power BI Desktop.
The Dual mode improves report performance by allowing Power BI to cache selected tables locally while keeping the rest of the data connected live to the Azure SQL source.

2. Prerequisites

Before switching to Dual mode, confirm the following:

• The entire report dataset is already configured in DirectQuery mode.

  ⚠️ You cannot set any table to Dual mode if the dataset is still using Import mode.

• You have access to Power BI Desktop and Model view.

• All tables are properly connected to the same Azure SQL Database source.

• You understand which tables should be optimized (usually dimension tables such as Calendar, Business Unit, Cost Category, Resource, etc.).

3. When to Use Dual Mode

Use Dual mode for small, frequently used dimension tables that are repeatedly referenced in visuals, slicers, or relationships.
This setting helps to reduce visual load time by caching these tables locally, while fact tables (such as Financials, Timesheets, or Resource Allocations) should remain in DirectQuery mode for real-time updates.

4. Steps to Switch from DirectQuery to Dual Mode

Step 1: Open the Data Model

1. In Power BI Desktop, open the report file (.pbix).

2. Go to the Model View (icon on the left pane — looks like a relationship diagram).

Step 2: Select the Table

1. In the Model view, locate the table you want to change (e.g., “Calendar”, “Resource”, “CostCategory”).

2. Click the table name once to highlight it.

Step 3: Change Storage Mode

1. With the table selected, look at the Properties pane (on the right).

2. Find the field labeled “Storage mode”.

3. From the dropdown, select Dual.

💡 If you don’t see the option for Dual, it means the dataset or connection is not fully using DirectQuery mode.
Check under File → Options → Current File → Data Load to confirm the model’s default storage mode is set to DirectQuery.

Step 4: Validate Relationships

After switching the table:

1. Check that the relationships between this table and others remain valid.

2. If any relationship shows a warning icon, revalidate by opening the Manage Relationships dialog and ensuring the link fields are consistent.

Step 5: Save and Test

1. Save the report.

2. Refresh visuals or apply slicers to confirm improved performance.

3. Observe that interactions are smoother — the Dual tables will now respond faster since they are cached in memory while maintaining DirectQuery for live fact data.

5. Important Notes

• The Dual setting does not improve performance while editing queries in Power Query Editor. Power Query always loads data directly from the source.

• Only dimension or lookup tables should be switched to Dual mode. Setting large fact tables (millions of rows) to Dual may increase memory usage and reduce performance.

• When publishing to Power BI Service, verify that the dataset continues to use DirectQuery connections for all fact tables.

6. Summary

By converting selected tables from DirectQuery to Dual mode, you can significantly enhance visual performance while maintaining real-time data accuracy.

Related Article:

Why We Choose DirectQuery Mode Instead of Import Mode (and the Advantage of Switching to Dual Mode) – OnePlan Support provided by PPM Works