Page 1 of 1
MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 4:24 am
by CRP0021
Hi all,
Just looking for a bit of advice compiling an MDX statement.
Here are some details:
I have a 2 dimensional cube called Sales which contains a Line dim and Measures.
I want to apply filters to the line dim based on values in the cube.
I'm able to apply filters properly when the value typed into the lookup cell matches exactly or is selected from a picklist, but I'm having trouble when the value typed into the lookup cell should be a wildcard search.
Here's a visual for more details. What I'm trying to achieve is the scenario 3 below.
Any advice would be greatly appreciated!
- Line Filters.png (42.67 KiB) Viewed 4720 times
Re: MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 7:00 am
by declanr
You can use INSTR e.g.:
Code: Select all
{ FILTER(
{ TM1SUBSETALL( [Line] ) },
INSTR([Sales].([Sales_m].[Manager]), "Bob") >0
)}
INSTR documentation:
https://docs.microsoft.com/en-us/sql/md ... rver-ver15
Re: MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 7:23 am
by CRP0021
Thank you Declan.
That works well.
One small wrinkle though if the lookup cell value is bob (lower case) instead of Bob this doesn't work.
I'd have to account for users typing in either I imagine.
I found this which actually works very well in PAW but not in PAX.
{FILTER([Line].MEMBERS,Instr(1,[Sales_m].[Manager],"bob",1)<>0)}
Re: MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 8:07 am
by Mark RMBC
Hi,
One small wrinkle though if the lookup cell value is bob (lower case) instead of Bob this doesn't work.
I'd have to account for users typing in either I imagine.
for PAX sheet,
imagine cell A1 contains the search text, e.g. Bob
To cater for lower or upper or any case input by the users you can do 2 things, firstly in another cell in the PAX worksheet have a formula something like =LOWER(A1), let us imagine that is in cell A2.
The MDX would then look something like this:
Code: Select all
="{FILTER([Line].MEMBERS,Instr(1,LCASE([Sales_m].[Manager]),' " &A2& " ',1)<>0)}"
regards,
Mark
Re: MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 8:18 am
by declanr
Mark RMBC wrote: ↑Thu Apr 21, 2022 8:07 am
To cater for lower or upper or any case input by the users you can do 2 things, firstly in another cell in the PAX worksheet have a formula something like =LOWER(A1), let us imagine that is in cell A2.
The MDX would then look something like this:
Code: Select all
="{FILTER([Line].MEMBERS,Instr(1,LCASE([Sales_m].[Manager]),' " &A2& " ',1)<>0)}"
You could also skip the additional excel cell and just put lcase around A2 also (in the MDX formula) - just for the benefit of keeping all of the "logic" in 1 place.
Re: MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 8:22 am
by declanr
CRP0021 wrote: ↑Thu Apr 21, 2022 7:23 am
I found this which actually works very well in PAW but not in PAX.
{FILTER([Line].MEMBERS,Instr(1,[Sales_m].[Manager],"bob",1)<>0)}
This is interesting - are you sure it is working differently in PAW to PAX?
This formula is pretty much the same as what I initially provided, the only real difference is instead of TM1SubsetAll it uses Members. This will give you the same result, as long as there is only 1 instance of each element in the hierarchy. If the same element exists under multiple parents, .members will give you multiple results.
But if it truly is working differently in PAW and PAX, then something sounds wrong. Both *SHOULD* be using the same engine.
Re: MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 8:29 am
by declanr
Just did a quick test. You are right, PAW is completely case insensitive and accepts upper or lower case for INSTR searches.
That's new information to me - I don't like the same MDX in different tools producing different results.
Re: MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 8:56 am
by Mark RMBC
Hi,
You could also skip the additional excel cell and just put lcase around A2 also (in the MDX formula) - just for the benefit of keeping all of the "logic" in 1 place.
Oh yeah!
Just did a quick test. You are right, PAW is completely case insensitive and accepts upper or lower case for INSTR searches.
That's new information to me - I don't like the same MDX in different tools producing different results.
News to me too and I don't like that either!
regards,
Mark
Re: MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 9:44 am
by gtonkin
CRP0021 wrote: ↑Thu Apr 21, 2022 7:23 am
I found this which actually works very well in PAW but not in PAX.
{FILTER([Line].MEMBERS,Instr(1,[Sales_m].[Manager],"bob",1)<>0)}
Not sure that your MDX is correct here as you dropped the reference to the Sales cube before addressing [Sales_m].[Manager]
Possibly this would work:
Code: Select all
{FILTER([Line].MEMBERS,Instr(1,[Sales].([Sales_m].[Manager]),"bob",1)<>0)}
Also, AFAIK, the 4th argument, that MS MDX says is ignored and is for compatibility, I have always used as a 1 to indicate ignore case.
I ran some rudimentary test in PAfE and setting the 4th argument to 0 only matches exact case, setting to 1 ignores case.
Re: MDX wildcard filter based on cube value
Posted: Thu Apr 21, 2022 11:18 am
by Keith Would
This is really helpful, thanks everyone.
While testing, I wrote the wrong dimension name in the cube, but it still worked (in PAfE)?
It doesn't work if you don't put in any dimension name.
Code: Select all
{FILTER([Permanent Staff].MEMBERS,Instr(1,[Current year staff forecast].([Year for payroll total].[Forename]),"an",1)<>0)}
'This one below works as well somehow, despite referring to the wrong dimension for Forename?
Code: Select all
{FILTER([Permanent Staff].MEMBERS,Instr(1,[Current year staff forecast].([Permanent staff].[Forename]),"an",1)<>0)}