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
MDX - FilterByPattern on Attributes
- garry cook
- Community Contributor
- Posts: 209
- Joined: Thu May 22, 2008 7:45 am
- OLAP Product: TM1
- Version: Various
- Excel Version: Various
-
- MVP
- Posts: 3120
- 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
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.
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
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
- 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
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!
KR
PS I like to keep the { }'s for the sake of my own sanity when checking the syntax!
-
- MVP
- Posts: 3120
- 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
I like to keep them out.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!
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
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