MDX CALCULATED MEASURES

Hello, please i am trying to count a set as a calculated measure, when this set is called directly in the row , it returns fast, but when i try to count the set as calculated measure(so i can slice with another dimension) the query keeps running forever, please any ideas.

the queries are below

select
{} on 0,
nonempty
(
{([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])}
,
{([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])}
)  on 1

FROM sax
------------------------------------------------
with
member
measures.lil
as
count(
//Exists
nonempty
(
{([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])}
,
{([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])}
))
select
 {measures.lil}on 0
FROM sax

Thanks

September 9th, 2015 5:07pm

Hi I4QR1A,

In this scenario, the first query only applies set of members on rows. The second one will do count aggregation on this set. However, since the two sets use different hierarchies, it supposed to throw error like below:

So you can only count number of members under same hierarchy.

Regards,

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 7:41am

You may try this :

with member measures.cnt as
 'count( {
(                                                      // remember to use paranthesis in case of different dimension
[date].[calendar].[date].members,[product].[product categories].[product].members
)
})'

 SELECT  measures.cnt ON columns
FROM [Adventure works]

you can use different hierarchies to get the count of their crossjoins, but they should form a tuple () in case of different dim or hierarchy and set {} for same hierarchies or a set of multiple similar tuples {(),()}

September 11th, 2015 3:00am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics