Filter by Attribute

[Chuks]

Hi All,

We have a dimension with some elements and an attribute associated with it,as below

Dim1
A
B
C
Attributes ( in the order of Dimension elements given above)
P1,P2,P3
P2,P4,P5
P5

Now we have a requirement to get all the elements from Dimension Dim, which has P2 in its attribute, so in this example A and B would be the expected result. Could you please let me know if its possible?

Thanks.

[Wim Gielis]

For example:

Code: Select all

{FILTER( {TM1SUBSETALL( [Dimension] )}, Instr([Dimension].[Attribute], "P2") > 0 )}

Combine this with function like LCase and UCase because Instr is case-sensitive.
Also, make sure that finding P2 with Instr does not lead to let's say P25, which contains P2.

[Chuks]

Hi Again,

Thanks Wim, it worked!

I would like to know if there is a possibility to create MDX query which does a filter by attribute on leaf level elements of the dimension and give us the leaf elements which matches the attribute value along with the parent of that leaf element.

Eg:
Dim1
Alpha(parent of below elements)
A
B
C
Attributes ( in the order of Dimension elements given above)
P1,P2,P3
P2,P4,P5
P5

MDX Query: Filter by attribute which has value as P2.

Expected Result:
Alpha
A
B

Thanks!

[jpm_de]

Hi there,

To start working with TM1's MDX functionality, you should take a look at this document:

Creating Dynamic Subsets in Applix TM1 with MDX - A Primer
http://bihints.com/book/export/html/68

And keep in mind, that you can do a lot of stuff with TM1, so there might be other ways (than the one's in other products) to achieve a task more easily.
Therefore is is of much help to describe the why you want to do something and not just the what your next step might look like.

Best regards,
JP

[Chuks]

Hi JP,

Thanks for the link! It has lot of useful functions listed. But actually I couldn't get any function from there to use for the requirement i stated below,

I would need a subset which displays both leaf and consolidation of that leaf based on the filter on the leaf elements. Please let me know if it can be created using an MDX expression.

Thanks!

[jduplessis]

Hi JP,

Thanks for the link! It has lot of useful functions listed. But actually I couldn't get any function from there to use for the requirement i stated below,

I would need a subset which displays both leaf and consolidation of that leaf based on the filter on the leaf elements. Please let me know if it can be created using an MDX expression.

Thanks!

You can use a function like generate. If what Wim provided worked for leaves try:

Code: Select all

{GENERATE(
   { FILTER( {TM1SUBSETALL( [Dimension] )}, Instr([Dimension].[Attribute], "P2") > 0 ) },
   { [Dimension].CurrentMember.Parent, [Dimension].CurrentMember }
)}

Not sure this is the best way to do this, sure someone will correct me if its not.