Calculated measures when live connected to MDX cubes
In May 2017 Power BI Desktop started supporting creation of DAX measures when working in live connect mode to an external SSAS model: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-feature-summary/
Vote on this idea if you want support for calculated measures against multidimensional models too.
Emily Bonnin commented
8 months ago I accidentally created a measure in my live connected environment and crashed the cube, not just in powerbi but in my excel reports as well. Realized I couldn't do this, tried to look for any documentation came up short, even the workarounds are not very good. Hopefully, after nearly 3 years of this ticket being open the PowerBI team will address this being an option.
Jiri Neoral commented
I found a workaround and covered it in this blog post. It is not ideal, but it works http://www.neoral.cz/2019/11/dax-for-analysis-services.html
Andrey Yakimov commented
@MRLuge - basically, use a published report or a gateway as your data source, which then allows you to put report-level measures on top of it.
With a non-personal gateway, report-level measures refresh directly from cubes. If you are pushing highly automated reports to many non-pro users - I would think you are using an on-prem gateway + premium work-space.
With no gateways - a little bit of a work-around, but still works:
1. Live-connect a PBi desktop report to an SSAS mdx cube.
2. Publish that blank report to a workspace (does not have to be premium).
3. Open a new report and connect to that published report as dataset
(go to:: Get Data / Power BI Datasets, find your blank MDX live-connect)
4. You now should be able to right-click/button-click New Measure on dims and measure groups in this second report
In this no-gateway mode, to refresh the data you will need to refresh the first and the second reports,in that order. While this is sub-optimal, it should be sufficient for less-automated/externally used reporting. Definitely sufficient for moderately complex reporting dev.
SIDE NOTE - Power BI will use the target SSAS Server version of DAX. Some of the more recent and very useful DAX functionality might not be available if the server DAX is outdated. Upgrade the server DAX if you can.
Could you share the method on how to add calculated measures when live connected on MDX cubes ?
Andrey Yakimov commented
FYI, If you are about to vote on this - Report-level measures on MDX have been working for a while now.
Please, provide an update on this. You could at least do that for an idea that was actually shared by Will Thompson, Product Manager of Power BI over two years ago!
Brent gamble commented
this would be great -- instead of waiting for a calculated measure to be developed in a cube we could do it this way and save a lot of time - provides even more flexibility
Please let us know something, even if it's we're never going to do anything so we can plan.
When can we expect this? I'm really surprised that Microsoft still has not incorporated it in Power BI while promoting this as a future BI tool...
Has anything happened regarding this idea or is it being ignored?
Please include creation of Dax measures when connected to an Multidimensional Cube.
This feature is very important for some analyses in Power BI, so that i think it must be on of the priorities to be improved.
Paul Southerland commented
This is needed to get data from an SSAS cube into a Power BI visualization. I was genuinely surprised that this functionality was not already in Power BI.
Brad Dietz commented
How is this not already a part of power bi? My company moved to a live connection and I can't do anything in power bi
Please include creation of Dax measures when connected to an Multidimensional Cube. It would be extremely helpful.
Please include this for multidimensional cubes as well. Within Excel it’s possible- why not within PowerBI?! Only then we can use PowerBI for our customers...
We need this feature on power BI
It's very important when user want to design more powerful report in power BI.
the limitation also limit the reporting in power BI.
can we please get this feature before YESTERDAY? :-)