Post reply

using count of dimension members as measure

  • September 17, 2010 at 4:19 am

    #157208

    Hello Folks,

    I am trying to use count of a dimension member as measure.

    My purpose is to count the distinct StoreID sales for the specified week of year. I have created a calculated member named StoreCount. And the formula of this calculated member is.

    Count(CrossJoin({[Measures].[Sale Unit]}, [Dim Warehouses MAX].Members), ExcludeEmpty)

    This calculation show the total count of the Sale Unit measure. But I have filtered this resultset for the week 17. And it still shows the all date range.

    Any idea?

  • September 28, 2010 at 8:37 am

    #1228417

    You are on the right track, and to get values for a particular time period, you have to crossjoin using the current context. Also, use DistinctCount instead of Count...

    Here is a sample query from AdventureWorks:

    WITH member [Measures].[Product Count] AS

    DistinctCount([Date].[Fiscal Weeks].CurrentMember * [Product].[Product Categories].[Product].Members * {[Measures].[Internet Order Quantity]})

    SELECT {[Measures].[Product Count]} ON COLUMNS,

    [Date].[Fiscal Weeks].[Fiscal Week] ON ROWS

    FROM [Adventure Works]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply