TM1 Forum

I have the following MDX which I am attempting to get a list of [Expense Center_d] from the [Template Assignment_c] cube where the [Assigned Template] equals any value in the subset of [Template_d] called [CES-HealthCareTemplates]

{filter({tm1filterbylevel({tm1subsetall([Expense Center_d])},0)},
(([Template Assignment_c].([Scenario_d].[Forecast],TM1Member([Period_d].[CES-Period].Item(0),0),[Tier_d].[Tier 1],[Template Assignment_m].[Assigned Template])=TM1Member([Template_d].[CES-HealthCareTemplates].Item(0),0))
))}

Currently the code returns [Expense Center_d] where [Assigned Template] is null. I replaced TM1Member([Template_d].[CES-HealthCareTemplates].Item(0),0) with an actual value from the subset (i.e. "Tier1-CGI-002") and that worked. I also tried TM1SubsetToSet([Template_d], "CES-HealthCareTemplates") and [Template_d].[CES-HealthCareTemplates] and it threw a nondesctipt error.

Any help would be apprecaited.
Thanks a bunch!
Trevor

it seems that the filter() function requires a static value on the right side of the comparison equation. You may need an alternative approach using attributes. Since you're already importing your valid [template_d] members into a cube, you could do a check against your "CES-HealthCareTemplates" subset at the same time and flag your [Expense Center_d] elements with an attribute value. This value would then be static and you could do the filtering on an attribute cube or support cube.