Exist v.s Existing and Auto Exist in MDX – Part 2–Usual confusion

Please read the Part1 of the same series to get the continuity

Usual confusions

• Exists and “Auto exists” are nearly same only difference is Exists function don’t display the second set .

Actually no. One set the context other doesn’t

SELECT [Measures].[Reseller Sales Amount] ON 0           
      , Exists( [Product].[Subcategory].[Subcategory].Members 
                ,[Product].[Category].[Clothing]             
              )on 1
FROM [Adventure Works] 
where [Product].[Category].[Bikes]

Above query will produce no results because we are slicing by two different members(Bikes & Clothing) at the same time. But the the query allows you to use the same dimension hierarchy in both rows Axis and slicer axis.

But the below query will produce errors, because it sets the context

SELECT [Measures].[Reseller Sales Amount] ON 0      
     ,  [Product].[Subcategory].[Subcategory].Members *
              [Product].[Category].[Clothing]

        on 1
FROM [Adventure Works] 
where [Product].[Category].&[1]

• 
  

  Existing keyword is just an extension of Exists, we can achieve the similar results with exist and currentmember function

Yes, But use of existing will improve the maintainability of code

Both the below codes will produce the same results

with member countofsubcategory as 
 count( existing [Product].[Subcategory].[Subcategory].Members)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
      , [Product].[Category].[Category].Members 
                on 1
from [Adventure Works] 

with member countofsubcategory as 
 count( exists( [Product].[Subcategory].[Subcategory].Members
                ,[Product].[Category].currentmember ))
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
      , [Product].[Category].[Category].Members 
                on 1
from [Adventure Works] 

• 
  

  DO we have not exists function ?

No. But this can be simulated with except function

The below query produces 8 rows of data

SELECT [Measures].[Reseller Sales Amount] ON 0      
     , Exists( [Product].[Subcategory].[Subcategory].Members , 
                      [Product].[Category].[Clothing]
                    )     

        on 1
FROM [Adventure Works] 

With the except function in the below query it produces the opposite set with 30 rows 

SELECT [Measures].[Reseller Sales Amount] ON 0      
     ,EXCEPT([Product].[Subcategory].[Subcategory].Members,
              Exists( [Product].[Subcategory].[Subcategory].Members , 
                      [Product].[Category].[Clothing]
                    )     
             )
        on 1
FROM [Adventure Works] 

• 
  

  Generate function forces it’s own context against existing keyword

Check the below query. It is clear that existing keyword uses the generate function’s context and that is why we got 3 subcategory against all category in the result set

with member countofsubcategory as 
Generate ([Product].[Category].[Bikes]
          ,  count( existing [Product].[Subcategory].[Subcategory].Members)
          )
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
      , [Product].[Category].[Category].Members 
                on 1
from [Adventure Works] 

 

This is an another proof that existing keyword is simply an extension of Exists function

Happy Reading