Forum Discussion
Excel and Power BI – better together
Hi Wyn,
Sometimes i create directly on Power BI Desktop, but usually prefer Excel. Some minor things - use Pivot Tables debugging and testing model; Evaluate table directly into excel sheet not only in DAX Studio; keep measures in separate sheet placing them witin the grid in functional, not alphabetical order plus ability to copy/cut/paste them.
Perhaps something else and perhaps some of above works in Power BI Desktop as well. All that is not critical, if in brief - i'm more comfortable with Excel.
The only point Power BI is on couple of cycles ahead of Excel from BI functionality point of view. Hope one day Microsoft solves Power Pivot compatibility issues and both products will be even more synced.
Hi Sergei,
Just out of interest, what is the reason behind your approach to keep measures in a separate sheet? I have come accross opinions advocating storing them within data tables to keep as close as possible to the source.
Thanks
Yury
Yury, for me that's easier to navigate and easier to maintain measures. If you have dozen of tables and few dozens of measures it's bit hard to find them if they are in different tables.
As for the source usually measures work with several tables, and one set of "basic" measures is the source for other ones. Thus doesn't matter to which table connect the measue, better to separate one.
That approach isn't my idea and isn't something uncommon. I guess was introduced some ago by people who used to work with SSAS.
To use or not, pros and cons - you may easier find the discussions related to the topic. IMHO, doesn't matter, depends on concrete case and concrete person.
Hi Sergei,
thanks for your comments. I have been using a separate measures table approach for similar reasons as you. Initially, I adopted it some time ago when the link between Power Query and Power Pivot was not stable enough, which was resulting in me losing some table connections and having to unload and reload tables. The reasons I am reconsidering this approach are
1. I have not encountered any issues of losing connections to Power Query in the last couple of years
2. I have come across instances, where pivot charts would not refresh after hitting 'Refresh All' button in Excel where measures are stored in a separate table
3. Apparently, the Q&A feature in PowerBI.com works better if measures are stored in database tables
4. If a measure refers to a single data table, it may make sense in keeping it as close as possible to the source
Just thought if a mixed approach may have a merit, depending on project requirements, off-course.
Yury
Hi Yury,
Thank you for sharing your experience. Can say nothing about Q&A, as for the rest it looks like i was more lucky, don't remember i had simular issues.