Multidimensional Cube data source - two date hierarchy giving issues in PBI not SSMS

[MungBurger]

Hi, 

I have a Multidimension cube as a data source. The date dimension has 2 date hierarcies. The only difference with the 2 is in the month year short name level and only for the current month. One uses Jun 2019 the other hierarchy will say "currrent month" for Jun 2019.

The reason we have 2 is so we can publish with the date slicer set to current month and that stays correct as the months progress. 

I have a calculation that in SSMS works correctly for both hierachies but in power BI when i display the calendar date hierarchy on the date axis it appears Power BI applies the page level date filter then does the calculation. When using the current month hierarchy on the chart axis it performs correctly. The end calculation uses a 12 month average of 1 measure and a 12 month total for another measure so the differences become obvious. Additionally in Power BI the values are repeated in groups of 3 so if it should be a value of 1,2,3 for Jan - Mar power bi will show 3 for Jan - March. ( the last quarterly value is repeated for the previous 2 months)

Why it works in SSMS and not in Power Bi is beyond me.

Oh and if i remove the page level filter for dates, (last 13 months) the calculation is correct for the calendar date hierachy.

[Stachu]

hi, I assume you're using the live connection correct?

to my knowledge SSMS is using MDX to query the cube, while PowerBI uses DAX to do so - they're different languages, and DAX has some known limitations when working with multidimensional
https://docs.microsoft.com/en-us/power-bi/desktop-ssas-multidimensional

if you have imported the tables to PowerBI, then you effectively created a new tabular model, and the multidimensional logic no longer applies. You should then create measures etc. in a way that works in tabular

[MungBurger]

Yes, direct query. Functionally it's the only practical option.
Turns out this was a complete rookie mistake. The workbook was connected to a different cube to what I was working on. Updated the connection and it's all good. #shame

[Stachu]

these kind of mistakes are also easily fixed, so that's a plus 😉