Dynamic Subset isn't working at Title

Post Reply
hisathishkumar
Posts: 6
Joined: Sat Nov 22, 2014 10:26 am
OLAP Product: TM1 and BI
Version: PA 2.0
Excel Version: PA

Dynamic Subset isn't working at Title

Post by hisathishkumar »

Hi all,
my first post here and kind of a newbie to TM1 PA.
I have created a dynamic subset filtered attribute(YearNo attribute on Employee Dim, and attribute value is an element from other dimension (Year dimension). When i placed the Employee DIM as Row or column its working fine based on the Year Dim filter which is at TITLE. however its not working when i placed both the EMPLOYEE and YEAR dimensions at TITLE (context).
Dim1 - EmployeeOrguHR and YearNo is attr
Dim2 - Year and YearNo_txt is an attr
following is the MDX on EmployeeOrguHR dimension

{FILTER( {TM1SubsetBasis()}, [EmployeeOrguHR].[YearNo] = [Year].[YearNo_txt])}

Highly appreciate any suggestions.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Dynamic Subset isn't working at Title

Post by gtonkin »

First thing to do is correct the MDX - TM1SubsetBasis() is probably unknown in this context - typically added when recording MDX.
Replace that with the [dim].[member] or set you are starting from and try again.
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: Dynamic Subset isn't working at Title

Post by Wim Gielis »

Hello,

Please read this lengthy page on MDX:
https://www.wimgielis.com/tm1_mdxstatements_EN.htm
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
Elessar
Community Contributor
Posts: 332
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Dynamic Subset isn't working at Title

Post by Elessar »

Hi, what interface are you using?
If it is tm1web, it will not work. such dynamic subsets work only when Year is in context and Employee is in rows/columns
"Both dims in filters" will work only in PAW (until you drag them out of the view to external selector widget)
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
hisathishkumar
Posts: 6
Joined: Sat Nov 22, 2014 10:26 am
OLAP Product: TM1 and BI
Version: PA 2.0
Excel Version: PA

Re: Dynamic Subset isn't working at Title

Post by hisathishkumar »

gtonkin wrote: Fri Jan 21, 2022 10:24 am First thing to do is correct the MDX - TM1SubsetBasis() is probably unknown in this context - typically added when recording MDX.
Replace that with the [dim].[member] or set you are starting from and try again.
Thanks for the response @gtonkin
i have used the following MDX later on, but still the same issue.

{FILTER( {TM1SUBSETALL( [EmployeeOrguHR] )}, [EmployeeOrguHR].[YearNo] = [Year].[YearNo_txt])}
hisathishkumar
Posts: 6
Joined: Sat Nov 22, 2014 10:26 am
OLAP Product: TM1 and BI
Version: PA 2.0
Excel Version: PA

Re: Dynamic Subset isn't working at Title

Post by hisathishkumar »

Elessar wrote: Fri Jan 21, 2022 12:34 pm Hi, what interface are you using?
If it is tm1web, it will not work. such dynamic subsets work only when Year is in context and Employee is in rows/columns
"Both dims in filters" will work only in PAW (until you drag them out of the view to external selector widget)
Thank you for the response @Elessar, apologies for the late response.


This is for PAW only, it is working fine when both YEAR and EmployeeOrguHR Dimensions in TITLE/filter. however, as you said, it is not working when i Drag EmployeeOrguHR dim out of the view and made them as a external filers(generic filters for multiple widgets on the same dashboard).

Is there any way to get that working? either by changing the MDX or changing something in PAW?
Thank you
Image
Attachments
tempsnip.png
tempsnip.png (93.11 KiB) Viewed 27100 times
Last edited by hisathishkumar on Mon Jan 24, 2022 11:47 pm, edited 1 time in total.
hisathishkumar
Posts: 6
Joined: Sat Nov 22, 2014 10:26 am
OLAP Product: TM1 and BI
Version: PA 2.0
Excel Version: PA

Re: Dynamic Subset isn't working at Title

Post by hisathishkumar »

Wim Gielis wrote: Fri Jan 21, 2022 12:28 pm Hello,

Please read this lengthy page on MDX:
https://www.wimgielis.com/tm1_mdxstatements_EN.htm
Thank you @Win Gielis, looks an amazing document. apologies for the late response.
User avatar
Elessar
Community Contributor
Posts: 332
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Dynamic Subset isn't working at Title

Post by Elessar »

hisathishkumar wrote: Mon Jan 24, 2022 11:39 pm
Is there any way to get that working? either by changing the MDX or changing something in PAW?
No. Somebody has already asked this on IBM community forum: IBM said that when you drag filter to external widget, MDX becomes disconnected from view.
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
hisathishkumar
Posts: 6
Joined: Sat Nov 22, 2014 10:26 am
OLAP Product: TM1 and BI
Version: PA 2.0
Excel Version: PA

Re: Dynamic Subset isn't working at Title

Post by hisathishkumar »

Elessar wrote: Tue Jan 25, 2022 7:12 am
hisathishkumar wrote: Mon Jan 24, 2022 11:39 pm
Is there any way to get that working? either by changing the MDX or changing something in PAW?
No. Somebody has already asked this on IBM community forum: IBM said that when you drag filter to external widget, MDX becomes disconnected from view.
Thank you for the quick response.
i came across the following article,they say it works when both are at Title, tried this logic, its throwing an error "Invalid MDX expression".

https://quebit.com/askquebit/IBM/how-to ... cube-view/

Thanks
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Dynamic Subset isn't working at Title

Post by Mark RMBC »

Hi,

As Ellessar has said twice already, what you are trying to do will NOT work if both dimensions are dragged outside the cube view.

I posted about this issue here, please read the whole thread:

https://community.ibm.com/community/use ... gestviewer

Hopefully IBM will resolve this at some point but feel free to add a comment saying you have the same problem, might help speed things along.

My workaround was to create some client attributes, have those as the selectors on the page and let the mdx reference those instead.

regards,

Mark
hisathishkumar
Posts: 6
Joined: Sat Nov 22, 2014 10:26 am
OLAP Product: TM1 and BI
Version: PA 2.0
Excel Version: PA

Re: Dynamic Subset isn't working at Title

Post by hisathishkumar »

Mark RMBC wrote: Tue Jan 25, 2022 10:37 am Hi,

As Ellessar has said twice already, what you are trying to do will NOT work if both dimensions are dragged outside the cube view.

I posted about this issue here, please read the whole thread:

https://community.ibm.com/community/use ... gestviewer

Hopefully IBM will resolve this at some point but feel free to add a comment saying you have the same problem, might help speed things along.

My workaround was to create some client attributes, have those as the selectors on the page and let the mdx reference those instead.

regards,

Mark
Thanks Mark, will try your workaround suggestion .
Post Reply