Dynamic Subset filtering on two attributes?

[jimicron]

Hi all,

Bear with the newbie again

I'm struggling with a dynamic subset and filtering on TWO attributes at the same time

My MDX is: {FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [SSD MPN] )}, 0)}, [SSD MPN].[Usage Type] = "Enterprise"), [SSD MPN].[Inclusion Flag] = "Yes")}

I hope that I am close??

Thanks as always!!!

[declanr]

Jimicron,

Below is how to filter on 2 attributes at the same time, I have removed the filterbylevel part as that was irrelevant to the topic but you can work out how to add that back in.

Code: Select all

{FILTER( {FILTER( {TM1SUBSETALL( [SSD MPN] )}, [SSD MPN].[Usage Type] = "Enterprise")}, [SSD MPN].[Inclusion Flag] = "Yes")}

Also worth noting for simple tasks like this you can simply record expressions in the subset editor and then view the created code in the same way you could record a macro and then view that code in Excel. Its an excellent way of learning basic concepts before you can then go on and tweak it for the stuff that the recorder can't quite do.

Also if you search for "MDX Primer" on this forum or google there was a pretty comprehensive document created a while back that covers pretty much everything you are likely to come across in terms of MDX requirements in a "standard" project.

[jimicron]

Thanks a LOT for the quick response!! I VERY much appreciate it!! I am in a meeting right now and had recorded, but must not have done something right A bit different with folks in the room then when on my own. This is totally helpful though and really appreciate the assistance!!!!!

[jimicron]

Hi all,

So, I am working on this again and was wanting to add to it... but am struggling with it The MDX works great but the end user would like to add to it and I wasn't quite sure how to add it since I can't record it. I will have to edit it manually. I think you will see why.

The code is:

Code: Select all

{FILTER( {FILTER( {TM1SUBSETALL( [SSD MPN] )}, [SSD MPN].[Usage Type] = "Enterprise")}, [SSD MPN].[Inclusion Flag] = "Yes")}

What I want to say is when [Usage Type] = "Enterprise" OR [Usage Type] = " " (blank) -AND- then [SSD MPN].[Inclusion Flag] = "Yes" OR [SSD MPN].[Inclusion Flag] = " " (blank)

It's the part of adding to it to also pull in the blanks. When I record it, I can't get the second half that I want which is why I don't just record it. Any suggestions. Thanks a lot! Appreciate the assistance!

[rmackenzie]

What I want to say is when [Usage Type] = "Enterprise" OR [Usage Type] = " " (blank) -AND- then [SSD MPN].[Inclusion Flag] = "Yes" OR [SSD MPN].[Inclusion Flag] = " " (blank)

I think you could try using the INTERSECT function (see here as well). Try this:

Code: Select all

{INTERSECT (
  {UNION(
    {FILTER ( {TM1SUBSETALL ( [SSD MPN] ) }, [SSD MPN].[Usage Type] = "Enterprise"  ) },
    {FILTER ( {TM1SUBSETALL ( [SSD MPN] ) }, [SSD MPN].[Usage Type] = ""  ) } 
  )},
  {UNION(
    {FILTER ( {TM1SUBSETALL ( [SSD MPN] ) }, [SSD MPN].[Inclusion Flag] = "Yes"  ) },
    {FILTER ( {TM1SUBSETALL ( [SSD MPN] ) }, [SSD MPN].[Inclusion Flag] = ""  ) } 
  )}
)}

Does that work?

[jimicron]

Thanks for taking a stab at it 'rmackenzie!' It works without error, but not working 100% correct. It got me closer though so thankful for the input!

I have a lot of alternate hierarchies in this 'attribute cube' and what it is doing is bringing in all the hierarchies and not just the one that I want. But it is also still bringing in all the "Usage Types" of "Client" and not just "Enterprise." BUT, it did bring in the blanks and also, the "Inclusiong Flag" half of this IS working. It's bringing in the blanks and the ones that are "Yes" and not bringing in the "no" - so again, that is positive.

This is treading new territory for me (didn't know you could do this with MDX - MDX is totally new to me, but I am familiar with SQL) so it's interesting and I am sure this will spark other things in my head. The unfortunate, is that I am just not famliar with this enough to probably figure it out on my own at this stage of learning. I will continue to try and play with it. However, also very welcome to more input from anyone. Thanks again!

[jimicron]

Thought I would post an image to help clarify I have tried various recordings and then tried to "fit it in" to the code above but must be missing something. I wasn't trained on understanding the order in MDX or anything so that's why it's a bit more difficult to understand at the moment. Here is a visual though that I hope is helpful. FYI - there are a LOT more attributes to the right of "Inclusion Flag" and "Usage Type".. I just kept these two in to help simplify and since it's the ones I am filtering on. Thanks again!

[tomok]

You realize, of course, that including the hierachy in the subset, and then filtering out some of the children is going to be highly confusing. For example, using your screen shot, any cube value shown for C120 is still going to include MTFDGAP016SAQ-1NAES, even though you've filtered it out of the subset. Filtering the display of children of a consolidation does not change the underlying definition of the consolidation. This is going to be true for any of the consolidated nodes where you have filtered out some of the children.

[jimicron]

Hi Tomak,

I just try to please my end users

But in seriousness, this would be a subset that is ONLY used in this cube that has all these attributes. So, no harm. It's filled with a ton of attributes and not any numeric sort of values. The dimension IS used in a financial cube, however, the end user realizes that she woudln't use it in that cube. It's just for her analysis of the cube with the attributes.

However, you did make me think that maybe I'll see if she minds not having the hierarchies... and just sort on the "SSD Model" column... then, all those that are the same will be next to one another. I am going to try that now Thanks for the input.

[rmackenzie]

what it is doing is bringing in all the hierarchies and not just the one that I want.

Then the answer is to not use TM1SUBSETALL({dimension}) but use DESCENDANTS(some_hierarchy):

Code: Select all

{INTERSECT (
  {UNION(
    {FILTER ( {DESCENDANTS ( [SSD MPN].[SSD Model Total] ) }, [SSD MPN].[Usage Type] = "Enterprise"  ) },
    {FILTER ( {DESCENDANTS ( [SSD MPN].[SSD Model Total] ) }, [SSD MPN].[Usage Type] = ""  ) } 
  )},
  {UNION(
    {FILTER ( {DESCENDANTS ( [SSD MPN].[SSD Model Total] ) }, [SSD MPN].[Inclusion Flag] = "Yes"  ) },
    {FILTER ( {DESCENDANTS ( [SSD MPN].[SSD Model Total] ) }, [SSD MPN].[Inclusion Flag] = ""  ) } 
  )}
)}

But it is also still bringing in all the "Usage Types" of "Client" and not just "Enterprise."

Hmmm, that shouldn't be happening... but it's hard to tell why without a copy of your dimension to hand

[jimicron]

Thanks a lot Robin!! That gets me darned near what i was looking for. It's good enough!! The new code is great and appreciate your assistance - was very helpful and thankful for your time!!