question

Matze-5251 avatar image
0 Votes"
Matze-5251 asked AlexeiStoyanovsky commented

MDX Filter function using CurrentMember (multidimensional)

Hello,

I am still quite new to MDX and need a measure, wich gives back a corresponding date.

The approach with filter() works properly as long as the tuple is defined by fixed elements.

But changing to CurrentMember leads to wrong results. Does this need just a simple modification or a total different approach?

Thanks in advance for any help.

Bests, Matze


138595-mdxsample5.png


sql-server-analysis-services
mdxsample5.png (25.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

Try Filter ( (existing [Date].[Calender].[Date]) as [DaysSet], [Measures].[MaxCapacity] = Max ( [DaysSet], [Measures].[MaxCapacity] ) ).Item(0).Name

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Matze-5251 avatar image
0 Votes"
Matze-5251 answered AlexeiStoyanovsky commented

Thank you very much Alexei for your help, it works and gives back correct results.

This is more than a "simple modification", interesting to see that there can be a reference to the set part inside the logical part of Filter function.
Unfortunately the performance is a bit weak.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That was a first midnight approach, so no wonder it can be suboptimal :) The source of the performance problem is obvious: what is saved as the set alias before the Filter loop is a list of individual relevant dates, and MaxCapacity for them is computed within the loop, quite possibly for each iteration. What should really be saved is the value of the MaxCapacity measure for the current cell. That'd be trivial in DAX with a var, but in MDX it'll probably require something hackish exploiting the fact that a set could consist of a single tuple.

0 Votes 0 ·
Matze-5251 avatar image Matze-5251 AlexeiStoyanovsky ·

Question over questions: do you might have an explanation why the CurrentMember does not work? Can DAX be used against a mulitdimensional modell? As far as I know MDX can be used against tabular.

0 Votes 0 ·

When Filter calculates the expression for a member of the set of Date.Calender.Date, this member is the current member of the Date.Calender hierarchy. This is what makes it problematic to obtain the total value of the measure for the set within the Filter to do the comparison.
MD cubes can be queried with DAX, this is what makes e.g. Power BI's live connection to MD cubes possible.

0 Votes 0 ·

In the meantime I've come up with a hackish candidate:

     Iif ( IsEmpty ( ([Measures].[MaxCapacity]) as hack.Item(0) )
          , null
          , Filter ( existing [Date].[Calender].[Date] 
                     , [Measures].[MaxCapacity] = hack.Item(0) 
                     ).Item(0).Name
     )
0 Votes 0 ·
Show more comments