SSAS 2005 using count of dimension members as measure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » SSAS 2005 using count of dimension members as...

SSAS 2005 using count of dimension members as measure

Rate Topic

Display Mode

Topic Options

Author

Message

Duncan Lawrence

Posted Friday, August 22, 2008 2:28 AM

Valued Member

Group: General Forum Members
Last Login: Friday, February 08, 2013 7:11 AM
Points: 73, Visits: 84

Hi, I have a dimension for Accounts. These accounts have a start date
for when they are first active and an end date for when they become
classed as "Former Accounts". As "Former Accounts" they are still to
be included in some reports so they are not inactive.

At the moment I calculate the balance on each account using
PreviousPeriod. To count the number of accounts with debit balances I
use;

Count(Filter(([Accounts].[Accounts].Members,[Measures].[Account
Balances C Fwd]>0)

To count the number of accounts with credit balances I use;

Count(Filter(([Accounts].[Accounts].Members,[Measures].[Account
Balances C Fwd]<0)

To count the number of accounts with zero balances I use;

Count(Filter(([Accounts].[Accounts].Members,[Measures].[Account
Balances C Fwd]=0)

And to count the total number of accounts I simply add the previous
measures together.

The issue is that the same number of Total Number of Accounts is
returned each period even though I know new accounts are added each
period. I think what might be happening is that in instances of
accounts where no transactions exist prior to the date selected, and
hence no balance has been calculated, the NULL balance is been treated
as zero.

Does this seem plausible? If I am correct how can I go about only
counting those accounts where there is a balance?

Thanks in advance for any suggestions or ideas.

Bye for now

Duncan w00t

All the best,

Duncan

Post #557117

john.wilder

Posted Wednesday, August 27, 2008 8:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 22, 2012 9:58 AM
Points: 7, Visits: 44

The question that comes to mind is how is the context being set for the evaluation of the expressions. The balance will be evaluated in the context of the current query, but the query will determine if the timeframe is something other than All. Can you post the entire query that is being used?

JW

Post #559673

« Prev Topic | Next Topic »

Permissions