Code: Select all
{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [dim_for_filter] )}, 0)},
[Cube1].([dim1].CurrentMember,
[dim2].CurrentMember,
[dim3].[Region]) =
[Cube2].([dim11].[Name], [dim12].[Region]) )}
That document I read of coursermackenzie wrote:The requirement seems really complex... can we ask the what practical purpose is?
Looking at your query, it seems odd that you reference .CurrentMember twice for different dimensions implying that an element in dim_for_filter would correspond to a similarly named element in dim1 and dim2. Is this the case? E.g. do you have an element 'X' in 3 different dimensions - this would be unusual, I think. Also, dim1 and dim2 are both in at least 2 cubes per your example. Obviously, this isn't unusual in itself, but it would be useful to know what sort of string data you envision both cubes containing.
This document has lots of useful information including how to filter a dimension on the value in a cube, which is half of what you want.
Code: Select all
TM1FilterByPattern(
{
TM1FILTERBYLEVEL(
{TM1SUBSETALL( [dim_for_filter] )}, 0
)
}
, [Cube2].([dim2-1].[el2-1], [dim2-2].[el2-2]...)
)
EP_explorer, I've gone back to your original point - and on re-reading it - it seems that you want to filter data in cube 1 based on values in cube 2. This implies that cube 2 holds values that correspond to element names in a dimension in cube 1. E.g. I can enter 'Region1' somewhere in cube 2, and then we see that element 'Region1' in the subset of a dimension that exists in cube 1, subsequently filtering data in the cube view.EP_explorer wrote:I want to write MDX query which will be filter values in the 1st Cube based on the values in the 2nd Cube.
AmbPin decison isn't correct and I explained whyrmackenzie wrote:EP_explorer, I've gone back to your original point - and on re-reading it - it seems that you want to filter data in cube 1 based on values in cube 2. This implies that cube 2 holds values that correspond to element names in a dimension in cube 1. E.g. I can enter 'Region1' somewhere in cube 2, and then we see that element 'Region1' in the subset of a dimension that exists in cube 1, subsequently filtering data in the cube view.EP_explorer wrote:I want to write MDX query which will be filter values in the 1st Cube based on the values in the 2nd Cube.
The bihints link, and example provided by AmbPin, are reasonable examples of how to achieve that. It's not clear to people why you can't use that approach which is well understood. Please can you explain in terms specific to your cube, why that approach doesn't work for you?
Yes you are almost right. But not "also matches a cell value in cube 3"rmackenzie wrote: Perhaps you are saying that you want to filter values in a dimension of cube 1, so that the value selected is the same as a cell value in cube 2 which also matches a cell value in cube 3? This is different and a lot more complex. Is that your problem?
EP_explorer wrote:There are 2 conditions -
1) if value in the second Cube is empty it shows all detail elements of dimension.
2) And if value in the second Cube is not empty it uses this value for filtering.
So you are questioning why condition (1) doesn't return what you want?EP_explorer wrote:I don't know how to join it if the value in Cube 2 is empty. Function Filter based on empty value will return no elements from dim_for_filter
If instead of empty you put "*" in the second cube, I believe my suggestion would work.There are 2 conditions -
1) if value in the second Cube is empty it shows all detail elements of dimension.
2) And if value in the second Cube is not empty it uses this value for filtering.
I noticed the fact that TM-1 doesn't support function IIFrmackenzie wrote:EP_explorer wrote:There are 2 conditions -
1) if value in the second Cube is empty it shows all detail elements of dimension.
2) And if value in the second Cube is not empty it uses this value for filtering.So you are questioning why condition (1) doesn't return what you want?EP_explorer wrote:I don't know how to join it if the value in Cube 2 is empty. Function Filter based on empty value will return no elements from dim_for_filter
Based on my understanding of the documentation, conditional logic per the Microsoft standard isn't supported in the TM1 implementation.
AlasAmbPin wrote:If instead of empty you put "*" in the second cube, I believe my suggestion would work.There are 2 conditions -
1) if value in the second Cube is empty it shows all detail elements of dimension.
2) And if value in the second Cube is not empty it uses this value for filtering.
Code: Select all
TM1FilterByPattern(
{
TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Time Monthly] )}, 0
)
}
, [System Information].([System Information Measures].[FilterVal], [System Information Values].[Value])
)
soon
It seems to me that AmbPin and Robin have tried to assist, AND moreover it seems to me that they as opposed to you have done the work in trying to elaborate on clarifying the problem. So if other people's interpretation of the issue isn't correct and you are serious about needing assistance then you need to do some work in explaining more detail exactly what the technical problem is, not others need to work harder to understand your description of the problem. If you want assistance that's the wrong way to go about it.EP_explorer wrote:I will be
Dimension which you have to filter ([Time Monthly]) by FilterbyPattern doesn't contain months
You have to choose element in Cube2 and using it trying to filter data in Cube1 but not in dimension as you are doing!!
If it were so simple as in your example I wouldn't ask it here.
Please - see all my examples attentively
As others have said, I think you need to be more attentive with your question. Everyone else here seems to know how to do this and is trying to help.Please - see all my examples attentively