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.