CODEWITHSUNDEEP
mdxssas-2012
Dmitry Gavrish
Dmitry Gavrish

Reputation: 3

MDX calculated member filtration uses measure

I need help with calculated member I have this code

CREATE MEMBER CURRENTCUBE.[Measures].[Summary distribution by CSKU]
 AS count(
    NONEMPTY(
        crossjoin(
                descendants ([05_Goods].[CSKU].currentmember,,LEAVES),
                descendants ([04_Agents].[Agents hierarhy],,LEAVES)
            )        
        )
), 
FORMAT_STRING = "###,##0;-###,##0", 
NON_EMPTY_BEHAVIOR = { [Quantity] }, 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Distribution' ,  ASSOCIATED_MEASURE_GROUP = '01_Sales'  ;

but I want to see a result without elements where sum([Measures].[Sales amount]) <> 0

How can I do it? Thanks!

Dmitry

Upvotes: 0

Views: 144

Answers (1)

ic3
ic3

Reputation: 7680

I don't see other choice than using the MDX Filter function :

 ...
 AS count(
   FILTER(  
    crossjoin(
            descendants ([05_Goods].[CSKU].currentmember,,LEAVES),
            descendants ([04_Agents].[Agents hierarhy],,LEAVES)
        )
    ,  [Measures].[Sales amount] <> 0)
    )

You might try adding the NonEmpty to the descendants method to improve performance (if some descendants have no [Sales Amount].

Upvotes: 1

Related Questions