MDX query with filter clause

Hi,

I have the following MDX query which is supposed to return the number of records with Actual time > TargetTime

for each customer, location and editor. Actual Time and Target Time are measures on the Fact table defined in minutes and represented by integer values.

The hierarchy I have created is

Customer Name

Location Name

Editor Name

The MDX query I have written is:

Count(filter(Descendants([DimCustomer].[Customer_Hierarchy].CurrentMember,[DimCustomer].[Customer_Hierarchy].[EditorName]), [Measures].[ActualTime]>[Measures].[TargetTime] ))

But this query gives me the number of customers rather than the number of records with ActualTime > TargetTime

for each customer, location, editor.

Any help would be appreciated.

The best approach here in my opinion would be to create calculated column in DSV returns 1 when ActualTime > TargetTime and 0 otherwise, and then define a new measure on top of this column with Aggregation Type as SUM.|||

I have implemeneted a dsv for the above with a measure named turnaroundtime to get the count of records with ActualTime > TargetTime

I also need to get the count of documents for each customer -> location -> editor where actual time > target time and edit type = Voice

Documents is the fact table. For each document in the fact table, the edit type field specifies if its 'voice' or 'other'

I was trying to get the filter clause to work for this.

|||

Can you create another dimension for Edit Type? If you did this, then you'd be able to filter a query using the new dimension, selecting the member [Voice].

HTH,

Dave Fackler

|||

I created another dimension for edit type but not sure how to write the calculated member.

I need the count of documents where Edit type = Voice and ActualTime > TargetTime

documents is the fatc table.

How do I write the query to get the number of documents for each customer -> location->editor.

I am new to MDX and not able to figure it out

|||

Assuming you want this as an additional calculated measure (and not just doing the filtering within a given query), you could add a calculated member named something like [Measures].[VoiceTurnAroundTime] with a calculation of "([Measures].[TurnAroundTime], [Edit Type].[Voice]")...

HTH,

Dave Fackler

|||I tried that but that displays an error.|||Can you provide the exact calculation as you have it typed in and the error being displayed?

Dave F.|||It worked...thanks a lot.