measure counting distinct values from a field

How do I count the amount of distinct values from a column in a fact table as a calculated measure

Assume the primary key of my fact table is a composite of three columns (a,b,c).

How can I code a calculated measure to count every distinct value of column a, not the amount of rows in my fact table.

Any advise help would be appreciated!

All you need to do is change the measure type from Count to Distinct Count ... make sure you select Column A as your key column....

MDX Question

Hi,
Assume all of members are {NewYork, London, Chicago, Miami, LosAngeles} in a dimension.

I want the members which name is lead of 'L' as MDX axis(0). That mean I want {London, LosAngeles} be MDX axis(0).

In Sql, we can use "like L%".
How to do this in MDX?

thanks,

You can use the MDX Filter() function in combination with various VBA functions such as Left(), Right(), Mid():

SELECT
FILTER([Cities].members, VBA!Left([Cities].CurrentMember, 1) = "L")) ON 0
FROM [Cube]

(I am reciting this from memory so the syntax might not be a 100% right, but you get the idea)|||thanks a lot...:)

Mdx query

Hello guys,

Assume I have a date dimention in a cube and I want to retrive data based on the StartDate and EndDate value. In short, I want to filter all records between StartDate value and EndDate value which is given by a user. Can any body give a general syntax or mdx query to achive this goal?

Sincerely,

Amde

Hello Amde

Assuming Analysis Services 2005:

SELECT {[Measures].[MyMeasures]} ON 0,

[MyDimension].[MyHierarchy].[MyLevel].members ON 1

FROM [MyCube]

WHERE {[Time].[Date].[<StartDate>] : [Time].[Date].[<EndDate>]}

|||

Thanks a lot michael,

But what if the the <StartDate> and <EndDate> values are parameter values that should be specified by a user? What will be the where condition in this case?

Sincerely,

Amde

|||The query itself needs to be the same. How would the user specify the parameters? Which client is the user using?|||

Hey michael,

That is a good question. I am going to write this query in the Report Designer; there will be report parameters <StartDate>and<EndDate> by which the user will specify and based on the parameter, I want to filter the record. I think this makes sense.

Sincerely,

Amde

|||

Ahhh... OK. Then I guess you just need to create two report parameters (for instance, named @.StartDate and @.EndDate). The values have to contain the unique member name for the dates. This could be something like [Time].[Date].&[20060518]. This value can the be passed to the MDX query. Now, I don't remember the exact syntax when you want to use report parameters in an MDX query, but I believe the WHERE statement should be something like this:

WHERE {StrToSet(@.StartDate.Value + ":" + @.EndDate.Value)}

|||

Hey Michael,

Thank you for sharing your idea, I know the syntax in reporting service; I will change the syntax and use it properly.

Sincerely,

Amde