SQL BI

My Experience - Troubleshooting SQL BI Stack

MDX: EXISTING

MDX: EXISTING

  • Comments 3

 

By default, sets are evaluated within the context of the cube that contains the members of the set. The Existing keyword forces a specified set to be evaluated within the current context instead.

 

with member [Measures].[k] as
([Product].[Category].currentmember,
[Measures].[Measures].[Reseller Sales Amount]
)
Select [Product].[Category].[All].Children on 1
,
Measures.k on 0
from [Adventure Works]
where [Geography].[Country].&[United States]
go

image

With Existing Function -


with member [Measures].[k] as
sum(existing([Measures].[Measures].[Reseller Sales Amount]
))
Select [Product].[Category].[All].Children on 1
,
Measures.k on 0
from [Adventure Works]
where [Geography].[Country].&[United States]

image

Comments
  • With all due respect, these examples have nothing to do with how to use EXISTING at all. Here's a much better example of how EXISTING can be used to filter a set based on the current cell context:

    with

    member measures.test1 as

    count([Customer].[Customer].[Customer].members)

    member measures.test2 as

    count(existing [Customer].[Customer].[Customer].members)

    select {measures.test1, measures.test2} on 0,

    [Customer].[Country].members on 1

    from [Adventure Works]

    You'll see that test1 always returns the same value, the count of the total number of customers in all countries, whereas test2 returns the count of the number of customers in each country on rows.

  • thanks Chris for adding ur valuable comments, i was just showing how to use Existing Function in next linked post i was about to show its functionality as the example which is given in MSDN Site is pretty complex to digest for naive users but the example which  you had given is very simple.

    thanks for adding value to my post.

    karang@microsoft.com

  • Nice post Chris. I was looking for Existing example this morning and your's was right on target. Thanks.

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post