When getting data for Power BI, the default setting is “Import” and not “DirectQuery”. Import doesn’t work too well for me at least, because my data sets are all changing every day. Here are some tips in converting a project to instead use DirectQuery.
- First, back-up your file and then open your new copy (of the .pbix file)
- Click “Transform Data”
- In the left sidebar, delete all of your queries
- Click Close and Apply
- Now you’ll see that obviously, all of your reports are broken. Now, let’s fix them. 🙂
- Click Get Data –> SQL Server, enter your db credentials and be sure that direct query is selected (it won’t be by default)
- Now, if there are still broken areas of your reports, open your back-up copy and make sure all of your transformations (such as Measures) are re-created.
There are limitations to DirectQuery, including the inability to use hierarchies in slicers. Check back in a few days for one workaround to this, but I plan to take heavy inspiration from https://community.powerbi.com/t5/Desktop/Possible-Tip-Trick-Dynamic-Date-Dimension-Table/td-p/8462