TM1 Forum

Hi all,

I am trying to create a source view within a TI process using MDX to filter based on Cube value.
So, the cube has 7 dimensions and I have created the source view based on the requirement as below which works fine

Dim1 - 1 Static element selected
Dim2 - 3 Consolidated elements selected
Dim3 - 2 Consolidated elements selected
Dim4 (Product) - All leaf levels in the dimension with MDX
Dim5 (Account)- Leaf levels from a specific consolidation with MDX
Dim6 - 1 Static element selected
Dim7 (Measure) - 'Amount'

Now the problem arises when I try create the MDX for filtering only those values in this view which are above 1000.
I used this MDX for Product in order to filter which does not work. I have been trying since long , have read posts in the forum and bhints MDX guide but still stuck.

{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},[Cube].([Measure].[Amount]) > 1000 )}

Any help is appreciated. Thank you.

That [cube] reference is too short. How do you know which Amount cells it is in the cube ?
Incorporate the other selections or hardcoded elements in the cube statement.
There are many many examples on it using BI hints or my webpage on MDX statements.

Another thing to consider would be if filtering the view by cube value is necessary, aka can the filtering be done in the data tab (of a TI process) rather than in the view itself.

In addition to Wim's post, as your MDX does not specify any references for the other dimensions in the cube it will be looking at the default members in each dimension other than product and measure
{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},[Cube].([Measure].[Amount]) > 1000 )}

The default member can be set using the }HierarchyProperties cube, and if nothing is set, then I believe it simply uses whatever is the first element in the dimension.

Thank you so much BurnStripe and Wim.

Wim, I didn't know that you have a Webpage. It's really helpful.

Nance1234 wrote: ↑Tue Sep 24, 2024 3:24 pm Thank you so much BurnStripe and Wim.

Wim, I didn't know that you have a Webpage. It's really helpful.

You're welcome