MD live useless? Totals of every table are wrong?

[tss68nl]

As Tabular is still not capable of holding our corporate data we are bound to using a multidimensional cube.

We are having a major issue with PowerBI connecting to an MD cube:

Lets say we have 2 measures: Revenue and RevenueLY. The first is a straight measure in the cube, the latter ofcourse a time-based measure that calculates the parallelperiod of the CurrentMember over the YQMD hierarchy (year-quarter-month-day)

We create a simple report in PowerBI:

- A table containing the months from the YQMD hierarchy, and the two measures.
- A slicer where one can select the desired months in the overview. 

- A slicer where I filter Year = 2018.

Let's say every month in this year has 20M revenue. Every month in last year has 10M revenue.

When I select just one month, the overview is correct. I get a month line with 20M Revenue and 10M RevenueLY.

When I select March - April - May for months, I get :
- A correct line for every month with 20M and 10M RevenueLY

- A total line with 60M Revenue (so far so good) and 120M RevenueLY... oops, thats the full year.

I inspected the slicing within the MDX cube, and although it does some magic on the normal measures, the MDX CurrentMember just points to the full year or the 'All' member, while the line should refer to { March, April, May }. I tried to find a common filter by using a Dynamic Set of months then finding the existing in the currect slice, and this does not work either. in this simple case.

Result is that all tables potentially display the wrong numbers in all reports. We have around 20 powerusers that build (self-service) their own department with reports, and they all suffer from this.

Things that complicate this issue:

- Slicers in PowerBI cannot be limited to single select: ctrl-click will still select multiple months although multiple select is off.

- One cannot forbid the use of total rows: there is a business need, and PowerBI makes them by default on every table

- There is still no option to create calculations on PowerBI side when connecting to an MD cube: the idea request (although heavily upvoted) was binned when the Tabular Cube connection got a calculation option. So there is no option to create DAX time intelligence as we still cannot create calculated measures.

Can someone please tell me how I can resolve this issue? Obviously a tool that potentially creates wrong totals for every time based measure would be of no use in a production environment. So I am hopeful something obvious has been overlooked by me, and PowerBI is actually a useful tool on MD cubes?

[tss68nl]

As Tabular is still not capable of holding our corporate data we are bound to using a multidimensional cube.

We are having a major issue with PowerBI connecting to an MD cube:

Lets say we have 2 measures: Revenue and RevenueLY. The first is a straight measure in the cube, the latter ofcourse a time-based measure that calculates the parallelperiod of the CurrentMember over the YQMD hierarchy (year-quarter-month-day)

We create a simple report in PowerBI:

- A table containing the months from the YQMD hierarchy, and the two measures.
- A slicer where one can select the desired months in the overview. 

- A slicer where I filter Year = 2018.

Let's say every month in this year has 20M revenue. Every month in last year has 10M revenue.

When I select just one month, the overview is correct. I get a month line with 20M Revenue and 10M RevenueLY.

When I select March - April - May for months, I get :
- A correct line for every month with 20M and 10M RevenueLY

- A total line with 60M Revenue (so far so good) and 120M RevenueLY... oops, thats the full year.

I inspected the slicing within the MDX cube, and although it does some magic on the normal measures, the MDX CurrentMember just points to the full year or the 'All' member, while the line should refer to { March, April, May }. I tried to find a common filter by using a Dynamic Set of months then finding the existing in the currect slice, and this does not work either. in this simple case.

Result is that all tables potentially display the wrong numbers in all reports. We have around 20 powerusers that build (self-service) their own department with reports, and they all suffer from this.

Things that complicate this issue:

- Slicers in PowerBI cannot be limited to single select: ctrl-click will still select multiple months although multiple select is off.

- One cannot forbid the use of total rows: there is a business need, and PowerBI makes them by default on every table

- There is still no option to create calculations on PowerBI side when connecting to an MD cube: the idea request (although heavily upvoted) was binned when the Tabular Cube connection got a calculation option. So there is no option to create DAX time intelligence as we still cannot create calculated measures.

Can someone please tell me how I can resolve this issue? Obviously a tool that potentially creates wrong totals for every time based measure would be of no use in a production environment. So I am hopeful something obvious has been overlooked by me, and PowerBI is actually a useful tool on MD cubes?

[v-cherch-msft]

Hi @tss68nl

Total rows can be turned off in Format pane. It seems it’s not supported to create measures when live connected to MDX cubes. You may vote for this similar idea.

Regards,

Cherie

[tss68nl]

Hello Cherch.

Turning off the total rows is not an option. About every overview needs total rows, I am sure about every BI application you ever viewed has total rows. I cannot tell a 400 people company to not use/want total or subtotal rows in any of their reports.... that would lead to a direct dismissal of PowerBI as a suitable BI tool. (and probably the dismissal of Microsoft as a BI partner altogether)

I already voted for that idea years ago with my old employers account. What microsoft did on that idea is enable calculated measures on Tabular Cubes, while the question is about MD Cubes! Answer an MD issue with a Tabular solution, is not what we asked nor need.

I think that idea will never get any follow-up. I get the feeling Microsoft is trying to make MD development as hard as possible by leaving out features. Tabular was presented as a new and easy way to do BI, while MD stays for more serious enterprise solutions. I'd want a clear Microsoft statement about the question: "Does MD have a future as the Enterprise cube system next to Tabular and will you invest in development?". 

Also, being able to create your own calculated measures is a workaround, it's not the core issue. I am having trouble with PowerBI making mistakes in the data it returns. The data in a simple grid is not correct: the totals are not correct, they are not even consistent throughout the line. A normal measure is scoped to the filtered items, while a time based measure is not. By actually giving back the correctly scoped measure in the total line, PowerBI clearly shows it knows what the purpose is to return in the total line. But all time-based measures are scoped differently. I would call that a proper mistake/issue/flaw with PowerBI.

My question was if I overlooked something, or that PowerBI really makes mistakes on time based MDX measures. If it does make mistakes (and it sure looks like it) this is an issue that needs resolving very fast, or you need to take out MD support altogether and just be honest about the fact that MD has no future,