Page 1 of 1
MDX Filter doesn't see value Calculation by CalculatedCount
Posted: Wed Aug 09, 2023 1:33 pm
by EP_explorer
I have a dimension and some elements in it have 0 weight
But I have to use Value of a Cube with the dimension in MDX Filter
Filter(dim_for_filter,Cube(...,[dimension].[sum_element consolidated elements with 0 weight])>0)
As I receive 0 on element [sum_element consolidated elements with 0 weight] I decided to calculate it by ConsolidatedCount. I did it, and in Architect I see nonzero but Filter anyway consider that [sum_element consolidated elements with 0 weight] contains 0.
Is it normal behaviour of Filter?
MDX see only normally calculated values (by hierarchy), but doesn't see Values calculated by Rules - using ConsolidatedCount.
Re: MDX Filter doesn't see value Calculation by CalculatedCount
Posted: Wed Aug 09, 2023 2:16 pm
by MarenC
Hi,
cant you do something like:
Code: Select all
FILTER(TM1SubsetAll([Dimension]), [dimension].currentmember.properties("MEMBER_WEIGHT")="0.000000" and [Cube].([Dim1].[Element],[Dim2].[element])>0)
That way you might not need the "sum_element consolidated elements with 0 weight" and just look at the value element.
If the above isn't relevant, then I would personally need more information than has been provided.
Maren
Re: MDX Filter doesn't see value Calculation by CalculatedCount
Posted: Thu Aug 10, 2023 9:42 am
by EP_explorer
Yes. You are right. Without example it is difficult to understand the problem
So I have a dimension with zeroweight elements (left on picture). Cube in which I have flags (in the middle) and MDX which have to filter CFR dimension based on Sum element (90000000000). As 90000000000 contains 0 so filter return nothing (right)
- 01.jpg (97.65 KiB) Viewed 1711 times
After it I add in Cube Rule whcih calculate 90000000000 using ConsolidatedCount. And filter starts working (actully I made mistake early) but I found another problem - on real volumes it works slowly than simple consolidation.
- 03.jpg (67.83 KiB) Viewed 1702 times
I think of create new dimension which contains only 90000000000 and details elements (based on Account dimension). It works faster but less convinient (I have to mantain 2 dimension - Account with zero weightd elements and new - Account_details).
May be anybody have another ideas?
Re: MDX Filter doesn't see value Calculation by CalculatedCount
Posted: Wed Aug 16, 2023 2:08 pm
by PavoGa
So what is desired is to return a set of the top level elements where at least one leaf element is populated with a flag value in the cube?
Instead of another dimension, consider adding an alternate hierarchy with the only components of the top level consolidation are the leaf elements:
--90000000000
903001
903002
903003
The leaf elements would be weighted to 1 so that alternate consolidation has the total of the flags.
The MDX for retrieving those would be:
Code: Select all
GENERATE(
FILTER(
FILTER( TM1SUBSETALL( [dimname].[alt hierarchy] ),
[dimname].[alt hierarchy].currentmember.Properties("ELEMENT_LEVEL") = "level number"),
cube.(filter conditions) ),
{STRTOMEMBER( "[dimname].[dimname].[" + [dimname].[alt hierarchy].currentmember.name + "]")})