SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSAS 2005 using count of dimension members as measure


SSAS 2005 using count of dimension members as measure

Author
Message
Duncan Lawrence
Duncan Lawrence
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 191
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

john.wilder
john.wilder
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search