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.

MDX Query Help...Please

Hi,

I have a fact table containing the following columns:

CustomerID

RegionID

CategoryID

ItemID

Price

Relationships:

A customer belongs to only one particular region and category

Each customer can buy the same item at different prices

The measures are LowPrice and AveragePrice

Query

For a particular customer, what was the customer price, average price and low price for some other combination of region and category ( this not the region and category,the customer belongs to)

For ex:

CutomerId RegionID CategoryID ItemID Price

1 1 2 1 2.00

2 2 3 1 6.00

3 2 3 1 4.00

Report should display for Customer 1 , Region 2 and Category 3 as

ItemID Price LowPrice AveragePrice

1 2.00 4.00 5.00

Iam stuck with this query for a long time now...Any help on the query will be appreciated.

Also, how would find the above result for all combinations of (regions and categories)?

Thanks,

Prash

Could you explain how you derive this result from the 3 rows of data - only 1 of which is associated with CustomerID 1?

ItemID Price LowPrice AveragePrice

1 2.00 4.00 5.00

|||

Thanks Deepak for looking into the query.

Here we are looking at report for (customer 1, region 2, category 3, item 1).

As shown in the fact table there are two records for (item 1 ,region 2, category 3) combination and therefore Iam taking an average and minimum of the prices from those two records for Item 1.

CusomerID RegionID CategoryID ItemID Price

1 1 2 1 2.00

2 2 3 1 6.00

3 2 3 1 4.00

So when customer 1 logs in and runs the report for (region 2,category3, item1), he should be able to see the following data

ItemID Price LowPrice AveragePrice

1 2.00 4.00 5.00

The 'price' is Customer's Price from the facttable, 'LowPrice' is the lowestprice for Item1 in (region2, category3) and 'AvgPrice' is the averageprice for the Item1 in (region2, category3) combination.

As explained before:

Customers belong to one region and category. But the reports should be able to display the low price and average price for an item for any combination of region and category.

Hope I have explained it properly. Please let me know, if you need any other details

Thanks,

Prash

|||

Hi Prash,

Assuming that there is a cube "sum" measure like [FPrice], a "min" measure like [LPrice] and a "count" measure like [FCount], then you can create these calculated measures (dimensions are [Customer], [Region], [Category] and [Item]):

[GlbPrice]: [FPrice] / [FCount]

[CustPrice]: ([GlbPrice], [Region].[Region].[All], [Category].[Category].[All])

[LowPrice]: ([LPrice], [Customer].[Customer].[All])

[AvgPrice]: ([GlbPrice], [Customer].[Customer].[All])

|||Thanks Deepak. That was helpful.