measure using calculated member

I have fact and 2 dimensions. i have to create a mesure using calculated member.

fact is having

fkey

ckey

monthid

hits

responses

CDIM is having

Ckey

ADateID

MonthDim is having

Monthid

MonthName

now i want a measure(XYZ) to be calculated using calculated member as Count of Ckey for AdateID = '20000101' for current member of monthdim. finally when i browse thru a cube it should display some thing like below.

Monthid XYZ

200601 1000

200602 2000

Thanks in adv

If you have a measure that is simply a row count of rows in the fact table called "Measures.FactRowCount" you can create the following calculation that would count the number of rows with AdateID = '20000101' given that you had a dimension "CDIM" with "AdateID" as an attribute:

CREATE MEMBER CURRENTCUBE MEASURES.XYZ

AS

(CDIM.AdateID.[20000101],Measures.FactRowCount),

FORMAT_STRING="#,#';

HTH,

Steve

|||

I tried this but same value is repeating for all my monthdim members. i used below query

select [Measures].[XYZ] on columns,

[Month Dim].[Month Dim Hierarchy].members on rows

from [MyCube]

--

And also I changed Expressions as below but not worked.

([Month Dim].[Month Dim].currentmember,[Cdim].[adateid].[20000101], Measures.FactRowCount)

Could you please help me

Thanks in adv

|||

Open up the cube editor and look at the "Dimension Usage" tab. Are all of your dimensions related correctly to the measure group?

- Steve

|||

Steve, they are correctly related. and i want to tell you that most of my dimensions are not directly related to fact table. they are reference thru a fact(this table is a dim for main fact) table (say FD). The measure i am creating is the distinct count of FD dimension for the selected month.

hope above info helps you to understand my problem

|||

Lets take this offline. You can email me at stevepon@.microsoft.com

It would be helpful if you could send me a copy of your project files so I could better understand the relationships.

Steve