I have a simple situation, in which there is an on premise MD cube that is consumed by PowerBI.
In this MD cube, I of course have a lot of calculated measures that give the YTD, LY_YTD (last year - year to date) etc etc. Basically, all these measures use the CurrentMember property of a certain time hierarchy.
To reproduce the problem, you only need a couple of things:
- A MD cube with some proper LY measures in them: Let's say a Revenue and Revenue LY (the latter just shifts any given period to LY)
- A simple PowerBI report, with a table containing year and months (from the proper hierachy), and the two measures. Add to that a slicer/filter on months and/or years to select what months to display in the table.
When you filter one single month, all seems well. The table displays the month line, and a total line copying the month values.
When you select multiple months (lets say March + April + May), the Revenue is the total of the selected months. The Revenue LY is however the total of all years combined. Or...if you set a slicer with a single selection on 2018 to filter the months list, it's the total op 2018. So it only filters to single select filters * slicers, not multiple select.
And don't say this is the intended behavior: PowerBI calculates the Revenue (straight sum measure) correctly, so it's clear it does now the intention and scope of the total line.
I tested with some MDX measures what is asked from the cube hoping it would actually ask for a set { March, April, May } ... but PowerBI asks the MDX cube directly for [2018] or [All] depending on the situation. So clearly there is something wrong with how PowerBI creates the DAX or how it then translates to MDX. When PowerBI asks in a simple table for the wrong member or set... there is nothing you can do in the MDX Cube to solve it.
Also, one cannot solve it through DAX, as the connection still doesn't allow calculated members on MD Cubes.
If it's smart to create DAX and not MDX directly is a whole other matter, but seeing Excel pivot tables never had issues with getting simple tables from MD cubes, I'd say PowerBI is still a huge step backwards.
Please solve this issue, as PowerBI is just not useable as a front end tool on a MD Cube this way. Best thing would be to just create MDX from PowerBI. It's easy, and it would even allow to make calculated measures in MDX through PowerBI as well to create professional solutions with a PowerBI front-end.
v-xiaoyan-msft
on:
export to excel
Idrissshatila
on:
Power Query Filter Rows Basic UI bug