MDX - FilterByPattern on Attributes

Post Reply
User avatar
garry cook
Community Contributor
Posts: 209
Joined: Thu May 22, 2008 7:45 am
OLAP Product: TM1
Version: Various
Excel Version: Various

MDX - FilterByPattern on Attributes

Post by garry cook »

Someone asked me today how to pull a wildcard search against an attribute in MDX. I must be getting rusty (I blame Tableau and PowerBI) as this turned out to be a bit more long winded than I thought and I ended up sticking at the following rather obtuse statement - in this case returning Employees which have *PR* in their Cost Centre attribute.

{ FILTER(
{ TM1FILTERBYLEVEL(
{ TM1SUBSETALL( [Employee] ) }, 0) }
, INSTR([Employee].[Cost Centre], "PR") >0
)}

Seems messier than it should be - any tidy up suggestions welcome!

KR
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX - FilterByPattern on Attributes

Post by Wim Gielis »

That's about the shortest you can have, IMO. Except the fact that { } are often not needed in MDX statements.
I wouldn't call it messy.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
garry cook
Community Contributor
Posts: 209
Joined: Thu May 22, 2008 7:45 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: MDX - FilterByPattern on Attributes

Post by garry cook »

Fair enough - thanks for the conf Wim, much appreciated.

KR

PS I like to keep the { }'s for the sake of my own sanity when checking the syntax!
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX - FilterByPattern on Attributes

Post by Wim Gielis »

garry cook wrote: Fri Jul 27, 2018 6:48 amPS I like to keep the { }'s for the sake of my own sanity when checking the syntax!
I like to keep them out.

We have ( ) [ ] so an overdose of { } is just too much !

Also, Notepad++ does not recognize { } correctly in the syntax highlighting if } exists for control objects.
This breaks down my syntax checking and therefore for my own sanity I keep them out as much as needed and possible.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply