Direct Query - Limits ability to manipulate data (removes the Data section)

[Anonymous]

I am new to Power BI and have tried to get up to speed on Direct Query vs. Import.  Most makes sense from the research I have done but I am still struggling with users saying that Direct Query limiting the ability to manipulate data. Obviously there is no "Data" section but I don't see where I can't perform the same functions that I can in Import.

Looking for some help on what I can't do from a data manipulation standpoint in Direct Query.

Thanks,

Z

[v-huizhn-msft]

Hi @Anonymous,

Yes, there is no data section in Direct Query model. There are some limitations in Power Query. For example, you can't create a new table, There is a 1 million row limit for returning data when using DirectQuery and so on. More details, please review the limiations part in articles: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery and https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about.

>> I don't see where I can't perform the same functions that I can in Import.

You can create column and measure by selecting File > Options and then Settings > Options > DirectQuery, then selecting the option Allow unrestricted measures in DirectQuery mode. Then right click the table->New Column or New Measure as follows(I test getting data from SQL Server in Direct Query Mode).



While not all functions are supported in Direct Query. Some function of DAX are supported. For example, Time intelligence capabilities are not available in DirectQuery. You will get an error if the function is invalid when you use it in Direct Query. 

Best Regards,

Angelia

[Anonymous]

Ooops also forgot to ask...

What does it really matter if there is no "Data" section?  Is there any functionality lost by not having this section?

Steve

[v-huizhn-msft]

Hi @Anonymous,

No "Data" section is a designed behaviour in Power BI, you can create an idea here. I test getting data from SQL Server in Direct Query Mode, you can create some calculated column or measure using DAX as the picture above shown. Please confirm you use Diect Query or Live connection.

Best Regards,
Angelia

[Anonymous]

Angelia -

I can get Direct Query.

My question - I still don't understand what the data section gives me that I can't get elsewhere within PowerBI? Even with out data (Direct Query) section I can create calc measures and columns.

Thanks,

Steve

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Yes, you create a table visual including all the columns, you will view all data you loaded.

Best Regards,
Angelia

[Anonymous]

Thanks Angelia!

I realize some DAX capabilites are not in Direct Query  - but would you say most DAX functions are supported in Direct Query?

Z