Based on my limited experience, Power Pivot has a maddening lack of smooth integration with Excel. Power BI is a separate install, I believe, so not currently an option.
I'm stuck with Office 2013 for the time being and can't upgrade or install anything. If I could use anything, I'd probably prefer to go back to SSRS. Maybe Informatica, or OBIEE or something.
I'm biased by spending so many years writing SQL, but I found it extremely cumbersome to do basic things in DAX even after I figured out how, and haven't seen any obvious advantages. But I do know for a fact that software newer than 2013 adds some crucial features that would make it better.
DAX and SQL are qualitatively different languages. They serve different use cases entirely.
DAX is designed solely for analytical queries using dynamic context (so you don't have to code for every user selected filter or relationship dependency in your model.) Its meant to be written once and then used interactively by your users. It has tons of helper functions like time intelligence, iterators and generators ..
SQL is obviously much broader in scope and functionality, but to match the level of built in context driven dynamism of DAX would require a frankly unmaintainable mess.
So yeah if you just need an answer to a single specific analytic query, just write a SQL query.
But if you need to create a measure to show weighted average pricing for any user selected combination of equities, time periods, markets, vendors, and 500 other dimensional attributes - and then create 5 more measures on top of that to show year to date, year over year, performance vs. overall market, simulated demand at a 5% decrease in price, and price excluding large institutional purchases - DAX is your friend.
Ya it's tough if you're stuck on 2013. Sometimes even random commands in excel are great and you find out they work at home but not at the version at work, or require office 365 (?!).
Access?
I recommend watching some youtube power bi videos for dax tricks. Lots of dax stuff on youtube. Or radacad.
I'm stuck with Office 2013 for the time being and can't upgrade or install anything. If I could use anything, I'd probably prefer to go back to SSRS. Maybe Informatica, or OBIEE or something.
I'm biased by spending so many years writing SQL, but I found it extremely cumbersome to do basic things in DAX even after I figured out how, and haven't seen any obvious advantages. But I do know for a fact that software newer than 2013 adds some crucial features that would make it better.