TM1 Forum

Posted: **Tue Jun 25, 2019 2:06 pm**

Seems to be a context problem. However, I have been unable to figure out the problem. Here is the query:

     FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Accounts Master].[Hier.001]), 0),
         [Account Mapping].([Version].[Active_02], 
            [sys.Websheet Control].[Input], 
            [Geography].[All Geography], 
            [HR Level].[All HR Levels],
            [Organization].[Function Support], 
            [lookup.Measure].[CalcFlag2]) > 0)

The Account Mapping cube has the following dimensionality:

Version
sys.Websheet Control
Geography
HR Level
Organization
Accounts Master
lookup.Measure

So the query is accounting for all the dimensions in the cube Account Mapping. This query works in PAW if using the Accounts Master/Hier.001 subset editor from a view on Accounts Mapping. However, if opening that subset editor on another cube, it will not return a set. And yes, I'm making sure the hierarchy Hier.001 is selected in the subset editor. Even tried this:

Code: Select all

     FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Accounts Master].[Hier.001]), 0),
         [Account Mapping].([Version].[Active_02], 
            [sys.Websheet Control].[Input], 
            [Geography].[All Geography], 
            [HR Level].[All HR Levels],
            [Organization].[Function Support], 
            [Accounts Master].[Hier.001].currentmember,
            [lookup.Measure].[CalcFlag2]) > 0)

Being that it works from the cube referenced in the filter and not from other cubes indicates to me a context issue, but every dimension is accounted for in the query! A number of the dimension elements (ex: All Geography) are consolidations.

The real problem is the query is failing to return the desired members in a TI. We use MDX for virtually everything and this type of filtering is quite common. What strange evil magic is at work here?

Posted: **Tue Jun 25, 2019 2:35 pm**

Okay. This is, surprisingly, the first time I have attempted to build a subset on a hierarchy with the FILTER using cube values as the filter. All the others were using attributes, TIFWIW.

Have also tried saving the subset that is returning a subset and then using TM1SUBSETTOSET, but that fails to return anything as well.

Posted: **Tue Jun 25, 2019 4:02 pm**

Apparently, this filter method using the cube and the alternate hierarchy does not work if not within the context of the filter cube itself. As this was part of a larger query, just switched to using the base dimension and StrToMember function with a GENERATE to resolve the overall requirement.

I think this should be considered a bug, no?

Posted: **Thu Jul 04, 2019 5:50 am**

Hi
Perhaps the problem is that the PAW does not know how to currentmeber?
As far as I remember for the operation of this filter, the [Accounts Master] element should be in the context of the view.

TM1 Forum

PAW: subset MDX works in one cube view, not in another

PAW: subset MDX works in one cube view, not in another

Re: PAW: subset MDX works in one cube view, not in another

Re: PAW: subset MDX works in one cube view, not in another

Re: PAW: subset MDX works in one cube view, not in another