CODEWITHSUNDEEP
sql-server-2012ssasmdx
Dilli Babu
Dilli Babu

Reputation: 129

MDX : How to get dimension count for filtered members

WITH MEMBER [Measures].[Country Count]
AS
Count(existing [Customer].[Customer Geography].[Country])

MEMBER [Customer].[Customer Geography].ClassA AS
SUM(filter([Customer].[Customer Geography].[Country],[Measures].[Internet Order Count] > 3000))

MEMBER [Customer].[Customer Geography].ClassB AS
SUM(filter([Customer].[Customer Geography].[Country],[Measures].[Internet Order Count] > 1500))


MEMBER [Customer].[Customer Geography].ClassC AS
SUM(filter([Customer].[Customer Geography].[Country],[Measures].[Internet Order Count] > 0))

SET My_Custom_Set AS
{[Customer].[Customer Geography].ClassA, [Customer].[Customer Geography].ClassB,[Customer].[Customer Geography].ClassC}

SELECT  {[Measures].[Internet Order Count], [Measures].[Internet Sales Amount], [Measures].[Country Count]} ON COLUMNS,
My_Custom_Set ON ROWS
FROM 
[Adventure Works]

Result:

Result

In the above query, How to get how many countries contribute class A, class B, class C, Here for any class the maximum country count 6 is displayed in measure 'Country Count' ?

Why Here existing is not providing the count of countries for current class ?

Upvotes: 1

Views: 9469

Answers (1)

FrankPl
FrankPl

Reputation: 13315

This does not work, as you are working within the same hierarchy: [Customer].[Customer Geography].ClassA is a new member of this hierarchy, and the FIlter result is not contained in it, hence EXISTING does not work.

The following query works (note that the counts are on the base sets):

WITH 
SET ClassA AS
filter([Customer].[Customer Geography].[Country],[Measures].[Internet Order Count] > 3000)

SET ClassB AS
filter([Customer].[Customer Geography].[Country],[Measures].[Internet Order Count] > 1500)

SET ClassC AS
filter([Customer].[Customer Geography].[Country],[Measures].[Internet Order Count] > 0)

MEMBER [Customer].[Customer Geography].ClassA AS
SUM(ClassA)

MEMBER [Customer].[Customer Geography].ClassB AS
SUM(ClassB)

MEMBER [Customer].[Customer Geography].ClassC AS
SUM(ClassC)

MEMBER [Measures].[Country Count]
AS
CASE 
    WHEN [Customer].[Customer Geography].CurrentMember IS [Customer].[Customer Geography].ClassA THEN
         ClassA.Count
    WHEN [Customer].[Customer Geography].CurrentMember IS [Customer].[Customer Geography].ClassB THEN
         ClassB.Count
    WHEN [Customer].[Customer Geography].CurrentMember IS [Customer].[Customer Geography].ClassC THEN
         ClassC.Count
    WHEN [Customer].[Customer Geography].CurrentMember IS [Customer].[Customer Geography].[All Customers] THEN
         [Customer].[Customer Geography].[Country].Count
    ELSE
         1
END


SET My_Custom_Set AS
{[Customer].[Customer Geography].ClassA, [Customer].[Customer Geography].ClassB,[Customer].[Customer Geography].ClassC}

SELECT  {[Measures].[Internet Order Count], [Measures].[Internet Sales Amount], [Measures].[Country Count]} ON COLUMNS,
My_Custom_Set ON ROWS
FROM 
[Adventure Works]

Upvotes: 2

Related Questions