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
TM1Member null values
-
- Community Contributor
- Posts: 126
- Joined: Sun Jun 29, 2008 9:33 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2016
- Location: Karlsruhe
Re: TM1Member null values
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.