MDX query with count distinct filtered on another measure, that plays with other filters

Question

in the SSAS AdventureWorks schema, I'm trying to figure out a distinct count of how many distinct customers have made Internet orders greater than $1000.

I attempted to make an MDX query like this, and it seems to almost work, except that new measure seems to ignore the slicer axis / WHERE condition, showing the count of customers for all countries, not just Australia:

With 
member [measures].[DistinctCustomersHighSales] as 
distinctcount(
   filter([Customer].[Full Name].Members, [Measures].[Internet Sales-Sales Amount] > 1000)
)
SELECT {
   [Measures].[Internet Sales-Sales Amount],
   [Measures].[DistinctCustomersHighSales]
    }
 on columns,
([Date].[Calendar Date].[Calendar Year].Members) on rows
FROM [Analysis Services Tutorial]
WHERE [Customer].[Customer Geography].[Country-Region].[Australia]

What am I doing wrong?

If I were doing this in SQL, I would be looking for something like this

 SELECT Year(OrderDate) as year,
     sum(SalesAmount) 
     count(case when count(distinct case when SalesAmount > 1000 then dc.customerkey end)
 FROM dbo.FactInternetSales  fis
    join dbo.dimcustomer dc on fis.CustomerKey=dc.CustomerKey
    join dbo.DimGeography  dg on dc.GeographyKey=dg.GeographyKey
 WHERE EnglishCountryRegionName='Australia'
 GROUP BY year(OrderDate)
 ORDER BY year

SouravA SouravA · Accepted Answer · 2015-09-11T16:04:54

Try adding EXISTING:

With 
member [measures].[DistinctCustomersHighSales] as 
distinctcount(
   filter(EXISTING [Customer].[Full Name].Members, [Measures].[Internet Sales-Sales Amount] > 1000)
)
SELECT {
   [Measures].[Internet Sales-Sales Amount],
   [Measures].[DistinctCustomersHighSales]
    }
 on columns,
([Date].[Calendar Date].[Calendar Year].Members) on rows
FROM [Analysis Services Tutorial]
WHERE [Customer].[Customer Geography].[Country-Region].[Australia]

Scope is a very important concept in MDX. Only when you add EXISTING, does the engine realize the slicer.

The initial set in the definition of member is :

[Customer].[Full Name].Members

Sets are static in MDX. So, this set by default contains ALL the customers. When you add an EXISTING, before forming the set, the context is checked. Based on the dimension usage(relationship) in your cube, it is able to filter the customers belonging to Australia. Thus it works.

MDX query with count distinct filtered on another measure, that plays with other filters

1 Answers