Power BI - Convert Direct-Query to Import

Power BI supports three types of connectivity: Import, DirectQuery and Live Connection. We generally use DirectQuery if the dataset is large and use Live Connection for models that have been created with SSAS. Once the Power BI file is created with DirectQuery, can we change it to Import?

This is something I was asked yesterday and my answer was Yes. We can the mode used, and get the data imported than referencing data via metadata.

How to convert DirectQuery to Import?

Let's create a file with AdventureWorksDW2014 with DirectQuery mode;

Once it is created, we can see the only Relationship View is visible as no data is imported.

Let's just create a visual and save the file.

Let me close the file and open it again. Once opened it again, if the file has been created using DirectQuery mode, I will be seeing a button right-bottom called DirectQuery: Enabled (click to change).

I can click on it and change the mode. This is what I get when it is clicked.

Let me select Import and continue. Now I see data imported to my Power BI file.

Power BI - Can we use DAX functions with DirectQuery mode?

Have you tried to use DAX functions for creating measures in Power BI when the data source connection is Direct Query? If you have tired, you have already experienced this issue. Is it possible to use DAX functions with Direct Query mode?

Yes, it is possible up to some extent. Let me take you through an example. Let's connect with AdventureWorksDW2014 using Direct Query mode and access DimDate and FactInternetSales tables. This brings three relationships between two tables, delete them all and create a new relationship between DimDate.FullDateAlternateKey and FactInternetSales.OrderDate column.

Now, if we try to create a measure for calculating YTD on SalesAmount using TOTALYTD, you will get an error saying;

Function 'DATESYTD' is not supported in DirectQuery mode.

What is the reason for this? The reason for this is, by default, DAX functions cannot be set for calculating measures when the mode is DirectQuery. However, it is possible enable some functions for this mode by enabling Allow unrestricted measures in DirectQuery mode setting. It can be opened with File Menu -> Options and Settings -> Options menu.

Once it is checked, you need to close the Power BI Desktop and open it. After that, you will see that you can use the function. Remember, even though you have enabled it, it does not allow you to use all DAX functions.