DirectQuery is a powerful connection mode in Power BI that allows you to query large data tables in real-time. This is especially useful for getting live data for short periods, such as data that refreshes every minute from a system.

However, DirectQuery can be easy to break. If you try to perform a transformation in the Power Query editor that is not supported by DirectQuery, Power BI will automatically switch the connection mode to Import. Once the connection mode is changed to Import, you cannot change it back to DirectQuery in the existing query. Instead, you must create a new query.

To keep DirectQuery working, you need to be careful about the transformations you perform in Power Query. Actively use the logic from SQL commands such as SELECT, FROM, WHERE, GROUP BY, and ORDER BY, and avoid custom M code. If you break query folding, the query will revert to Import mode.

To see if query folding is still working, right-click on the last applied step in Power Query and check for the option "View Native Query." If this option is greyed out, query folding is broken.

What I learned from Helen's Linkedin post, it helpful to think of DirectQuery as being synonymous with query folding. This made it easier to determine which transformations were supported by DirectQuery and which ones were not.

Here are some additional tips for keeping DirectQuery working:

• Avoid using calculated columns in DirectQuery models. Instead, use DAX measures to perform calculations.

• Avoid using complex data types in DirectQuery models. Instead, use simple data types such as integers, strings, and decimals.

• Avoid using relationships between DirectQuery tables. Instead, use calculated columns or DAX measures to create relationships between the tables.

• Use the DirectQuery performance analyzer to identify and fix any performance issues with your DirectQuery model.

Source: https://www.linkedin.com/feed/update/urn:li:activity:7114763541957509120/