Hello, Please is it possible to use the exist function on different dimension, I am trying a query like this

with

member
measures.lil
as

count(
Exists
(
{([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])}
,
{([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])}
))

select
measures.lil on 0


FROM  sax

or is there a better way

Thanks

• Edited by I4QR1A Tuesday, September 08, 2015 2:30 PM

Hi I4QR1A,

In MDX, the exists function will compare two sets to get the common part. For members from different dimensions, even they may have same member name, they are still different members. However, within a set, we can not only have members, but also have tuples. So we can create the measure to the get the member name of a member, then we compare the tuples in two sets and get the count. Please refer to sample query below;

with member [x] as
[Date].[Calendar].currentmember.member_name
member [y] as
count(exists({([Date].[Calendar].[Calendar Year].&[2007],[x])},
{([Date].[Calendar].[Calendar Year].members,[x])}))

select {[y]} on 0
from
[Adventure Works]

Regards,

Thanks simon, from what you explained the function cannot be used for different dimensions, excepts the sets in the dimension is transformed into a tuple to get like a measure, then exists function can be used.