Forum Discussion
Convert storage mode from direct query to import, data source as power bi datasets
Hi,
Is there a way to convert the storage mode from direct query to import mode without having to delete and redo the entire model?
I have a report where I pull multiple power bi datasets, in which I apply additional tables and modelling to link these datasets together. The report is loading very slowly (especially when creating calculated columns and measures), so I want to convert the storage mode into import to improve the load time. However, the storage mode dropdown is greyed out:
.
Is there a way I can change the storage mode? Thank you!
Hi Anonymous ,
You can go to Power BI Desktop, go to Model view-->Advanced and change the storage mode from DirectQuery to Import .Similar issue is also resolved in this particular community post . You also follow that post :
https://community.powerbi.com/t5/Desktop/How-to-change-direct-query-to-import-query/m-p/100996Thanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
23 Replies
Adding a comment here because this was the only post I could find on the subject and I hope it helps future searchers. I think semantic models technically use "live connections" and aren't really import or directquery. It does use directquery technology when you combine it with other sources to make a composite, though.
https://learn.microsoft.com/en-us/power-bi/connect-data/service-live-connect-dq-datasets
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-modelsYou can get performance issues sometimes if you are combining a semantic model with anything else:
https://www.sqlbi.com/articles/strong-and-weak-relationships-in-power-bi/In this case I don't think there is any way to do import on a semantic model. This is a good usecase for data flows, however:
These solutions no longer work.
I am not gifen an option to change in the Model View
The Staus Bar does not have the "Change" option
and I even tried the trick of creating a new calculated column. It let me create one.
I also added a new file and made it Import mode, and I can go to the Tables view and that is the only table that shows up. The other tables show "Direct Query" on a gray background, and there is no way I have found to change it.
Anyone have any additional tricks?
Claude Van Horn
Try creating a calculated table from the origial direct query table, not just a calculated column. And then use that calculated table for all relationships, visuals, etc. That calculated table will be in import mode.
I'm not positive how I can create a calculated table from the Direct Import Table, but even if I could, wouldn't I still have to wait for the Direct Query table to search and load every time I change a parameter? I'm trying to eliminate the delays caused by the Direct Query.
Am I missing something?
PS. I figured out how to create a new table from the Direct Import query, but there's not enough resources to do that.
So I'm still looking 🙂
Thanks for the help!
Claude Van Horn
Hi Anonymous ,
As far as I know, it is not possible to convert import mode to direct query mode, But direct query mode can be converted to import mode
If you really need to change it to direct query,
my suggestion is to create a new one using direct query then copy your applied steps from your previous report.
It will be quite time consuming but it will achieve your requirement.
Thanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos CYou aren't even reading the question.
Wow, how did this question get marked as resolved? Reading through this thread I can only tell that so far you haven't understood the original question, which others are asking too, and I am experiencing.
The following Microsoft articles describe that we should be able to change the import mode for semantic model tables and I certainly can't because the option is greyed out:
<https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode>
<https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models>
Agree. I had the same issue and posted the solution I came up with, but it got placed in the middle of the thread for some reason. Here's what I did and it worked for me. And I've done it on two different reports and it worked for both. And on the second report, it was simpler because I was only pulling in one direct query table from another Power BI dataset. In that case, all you have to do is create a calculated table in your report that selects all the columns from that direct query table, and then build all of your relationships and visuals off of your calculated table instead of the direct query one. That worked fine for me.
I had this issue and was able to work around it. I believe my situation was similar to the original post in that I had a PBIX desktop report with several imported data tables from various sources. I then wanted to get data from a published Power BI dataset and be able to create relationships between that data and the other data within my report. To do this I 1) created a calculated table for each of the Power BI dataset tables I wanted, removing extra columns I wasn't interested in using the SELECTCOLUMNS function, naming each column something unique by simply prefixing the column name with the name of the original table - this is required for a future step to work, 2) created relationships between those newly calculated tables, 3) created a calculated table using NATURALINNERJOIN to combine all of those tables together into one (which will use the relationships for the join conditions and requires unique column names to work in DAX). Now I have an imported table with all of the data from the Power BI dataset that I was interested in that can be used across the other data that has been imported. Hope this helps for you.
Hi pratyashasamal , thank you for your reply!
I want to convert from direct query mode to import mode, which you mentioned is possible. Could you advice on how to do so?
Hi Anonymous ,
You can go to Power BI Desktop, go to Model view-->Advanced and change the storage mode from DirectQuery to Import .Similar issue is also resolved in this particular community post . You also follow that post :
https://community.powerbi.com/t5/Desktop/How-to-change-direct-query-to-import-query/m-p/100996Thanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos CHi pratyashasamal , Thank you for your reply.
The option to change storage mode is greyed out for me (please see my original post for a screenshot of what I mean.) Do you know any possible reasons that can cause this and if there are any ways to change this? Thank you!
I had this issue and was able to work around it. I believe my situation was similar to the original post in that I had a PBIX desktop report with several imported data tables from various sources. I then wanted to get data from a published Power BI dataset and be able to create relationships between that data and the other data within my report. To do this I 1) created a calculated table for each of the Power BI dataset tables I wanted, removing extra columns I wasn't interested in using the SELECTCOLUMNS function, naming each column something unique by simply prefixing the column name with the name of the original table - this is required for a future step to work, 2) created relationships between those newly calculated tables, 3) created a calculated table using NATURALINNERJOIN to combine all of those tables together into one (which will use the relationships for the join conditions and requires unique column names to work in DAX). Now I have an imported table with all of the data from the Power BI dataset that I was interested in that can be used across the other data that has been imported. Hope this helps for you.
