Search code examples
ssasmdx

Why does EXISTS return tuples with some nulls included


I must be misinterpreting the function EXISTS.
Why does the following return lots of customers with null revenues?
What function should I be using or am I right to use EXISTS but need to use it differently?

WITH SET [CustomerSet] AS
    EXISTS( 
    [Customer].[Customer].MEMBERS,
        (
        [Date].[Date - Calendar Month].[Calendar Month].&[201312],
        [Measures].[Revenues])
        )
select 
   [CustomerSet] on columns
from [ourCube]

Solution

  • Ok - EXISTS basically mimics auto-exists behaviour without having to include two levels of a cross-join in the resulting cellset. If the two arguments are not from the same dimension then auto-exist behaviour does not occur - this is the reason for the failure of my original script. There is an optional third argument, which is very useful in this situation, and creates a context using a chosen measure group for auto-exist behaviour:

    WITH SET [CustomerSet] AS
        EXISTS( 
        [Customer].[Customer].MEMBERS,
        [Date].[Date - Calendar Month].[Calendar Month].&[201312],
        "Revenues Measure Group"
        )
    SELECT 
       [CustomerSet] ON COLUMNS
    FROM [ourCube]