I have already write a blog post about existing limitation when using Power BI live connected to Analysis Services Multidimensional here. But recently I found out workaround which used not to work but does work now (and hopefully it will stay this way). But first brief introduction of the business problem
(if you can’t wait just scroll down to workaround part).
Introduction
Analysis Services can be installed in 2 main flavours. Tabular which is used in Power BI backend (first version introduced in Power Pivot for Excel 2010) and Multidimensional much older then that.
Native language of Multidimensional is MDX, native for Tabular is DAX.
In theory you can query both installation types using both languages and I do use MDX to query tabular a lot when developing Reporting Services reports.
And I also tried the other way around to query Multidimensional with DAX. And it works in management studio.
For approximately 3years I’ve been waiting for option to write custom DAX measures when live connected to SSAS MD.
I thought that something small has to be developed in order to make it work and it is not a priority. After digging deeper and talking to product team I found out it is exactly the opposite case.
DAX works on tabular structures and Multidimensional structures are just way too different. So some principles like iteration over table in case of SUMX wouldn’t work and doesn’t make sense.
In better cases DAX just wouldn’t work in worse cases it would lead into unpredictable results.
So the team did extra work in order to block it in UI of Power BI Desktop.
But this blocking all DAX leads into cases where you need something simple like Sales in Thousands in Power BI table and you are forced to change calculate script of your enterprise SSAS
model for sake of Power BI report...
And this is unfortunate at least.
Word of caution
But before you’ll apply the workaround just keep in mind that in can lead to incorrect results when using more complex DAX or it simply won’t work.
If it doesn’t work for you don’t blame Microsoft and don’t blame me :)
Workaround
It is rather simple first create blank Power BI workbook connected to SSAS Multidimensional using live connection
If you try to create new measure right click measure group and you can just expand all/collapse all.
(And last few years I was just few minutes before collapse because of this limitation)
Then save the file and publish to Power BI Service. I named it SD_PRD_Main (Shared Dataset PRoDuction Main) PBIX
Then create second blank Power BI file and choose Power BI Dataset as a source
Search the list
Here you go :)
Conclusion
Use it with caution, more complex DAX doesn’t have to work as stated above. But simple stuff like Measure in thousands. Actual vs Target etc... These can be done directly on report level.
This approach has other benefits like having one centralized connection string which can be used in 20-50 and more reports.
So you just change one connection if you need to perform failover to different environment/server.
Only outstanding problem I now have is how to change existing reports in batch to map them from SSAS MD live connection to Power BI Dataset as a source.
I heard it could be possible using Power Shell but I wasn’t able to make it work.
Enjoy your report level calculations. If it doesn’t work for your don’t complain to Microsoft. Because if you do, they could block the feature for good ;)
