I bet everyone who comes here can agree on one thing .. that DAX is not intuitive for the traditional Excel user/dev (?). Well, I too I was fighting DAX, and then one day I took out my Excel brain and put in my Access brain and tah dah !! DAX made sense. In fact DAX became almost easy, except for those really out there “tricks” that mostly don’t seem to apply to anything I would REALLY do anyway. But we’ll see about that I guess.
I must admit that I was lucky to have experience with OLAP that goes back sporadically for over 20+ years. Also, as I have mentioned here before, I have been doing data-driven spreadsheets including Pivot Tables for nearly 30 years. But it turns out that it was my experience with Microsoft Access that REALLY gave me a leg up on PowerPivot and DAX. Understanding relational database concepts was the keystone of that learning.
Even moreso than my Access experience helping me with DAX was how, thanks to Access, I was able to intuitively understand relationships and especially the power of what I call “pre-aggregation” to “massage” or transform my source data into the proper Star Schema format appropriate for use in PowerPivot. I have been criticized for being “obsessed” with the data more than the cool front end stuff in PowerPivot (DAX) but IMHO the front end means nothing (except a good demo) without a solid backend good data over and over and over again.
So here’s the deal !!
1. If you’re only going to use Excel as a workspace for the creation of PowerPivot-driven Pivot Tables for use in SharePoint I believe that a background in Microsoft Access is more useful than anything you’ve ever learned in Excel.
2. If you are an Excel Pro who wants to get into PowerPivot, but who doesn’t have a background in relational databases, I suggest STRONGLY that you reach out to that Access guy/gal in your office and become a team to develop PowerPivot solutions (to your mutual benefit).
3. Even better, you should consider seeking out an independent Microsoft Access developer and pay that person a premium to get your data together for your PowerPivot work. They will be able to easily and quickly clean up your data structures or at least tell you what sources are or aren’t going to be reliable for you. You may also be surprised at what that guy/gal can help you with far beyond PowerPivot and DAX.
4. If you are an Access developer you MUST GET INTO POWERPIVOT !! For the last few years ALL of my reports for my Access solution have actually been “dumps” to Excel. Now I am integrating PowerPivot into these “reports” from your databases I definitely add value beyond anything I could ever imagine before. It is a great addition to your offerings.
Just a thought…
p.s. I see a lot of “lurkers” here … please feel free to add any comments you might have. I’m curious what you think of my musings …. If you’re going to get personal just email me at firstname.lastname@example.org BTW😉