DAX query to Multidimensional SSAS works incorrect

Question

When Excel is connected to a SSAS, Excel makes mdx queries to the cube and it works correctly.

When PowerBI is connected to the SSAS (live connection), PowerBI makes DAX queries to the cube and it works incorrectly, because 'CurrenteMember' not works.

I have a measure in SSAS:

CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative amount]
 AS sum(
        YTD([Time].[Year - Quarter - Month].CurrentMember)
        , [Measures].[Fact]
    )

When I connect to the OLAP from Excel and choose August and September in Pivot Table, Excel makes mdx correct query to ssas and I see correct values.

In PowerBI I have Hierarcy filter (year-quarter-month) and grid with columns: [month], [Cumulative amount]. When I connect to the OLAP from PowerBI and choose August and September in the Hierarchy, PowerBI makes DAX query:

  EVALUATE 
  ....
  KEEPFILTERS(
    FILTER(
      KEEPFILTERS(
        SUMMARIZE(
          VALUES('Time'),
          'Time'[Year.Key0],
          'Time'[Year],
          'Time'[Quarter.Key0],
          'Time'[Quarter],
          'Time'[Month.Key0],
          'Time'[Month]
        )
      ),
      AND(
        'Time'[Year.Key0] IN {DATE(2019, 1, 1)},
        ('Time'[Year], 'Time'[Quarter], 'Time'[Month]) IN {("y.2019", "3q 2019", "August 2019"),
          ("y.2019", "3q 2019", "September 2019")}
      )
    )
  ),
  ....

And in this case it not works: values in the table are empty, and because of this the table is empty. If I choose only one item in the hierarchy (one month or one quarter) - it works good.

Is PowerBI not able to make mdx queries? What must be done so that DAX requests to multi-dimensional SSAS are executed correctly?

If you open the Time dimension designer in Visual Studio and click on the Month level of the Year - Quarter - Month hierarchy and tell me the name of the attribute which makes the level. Is there a little yellow warning sign on the hierarchy indicating it is not natural? — GregGalloway

BOUBRIT Nacim BOUBRIT Nacim · Accepted Answer · 2020-11-08T17:04:30

Thank you for your well asked question.

You should know that the problem is not in Power BI generated query.

You said that:

When I connect to the OLAP from Excel and choose August and September in Pivot Table, Excel makes mdx correct query to ssas and I see correct values.

Your end users will get confused, is the result show year-to-date till August or till September. And this is where the problem is.

We need to know that the YTD() function takes only one specified member and only one. (Either September OR Auguest) which is the logical way of things.

Ytd( [ Member_Expression ] )

Returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level in the Time dimension.

But there is a way to come around this, that I don't recommend.

Let's say that we want to show YTD result for the most recent month, if the user chooses many months (September, august, January) we take September to compute outed because it is the most recent.

This can be done by using two functions: Order and Item

CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative amount]
AS sum(
       YTD(
           ORDER(
                 [Time].[Year - Quarter - Month],[Time].[Year - Quarter -Month].MEMBER_KEY,BDESC
                ).ITEM(0)
          ) ,
          [Measures].[Fact] 
       )

DAX query to Multidimensional SSAS works incorrect

1 Answers