MDX For Dynamic Average Over Range Dimension

Post Reply
N13
Posts: 1
Joined: Sun Jan 04, 2009 11:50 am

MDX For Dynamic Average Over Range Dimension

Post by N13 »

Hi,

I have a fact table that contains the following columns:
Id, UserID, Score, Date

I also have a score range dimension with the following members statically predefined:
0-10, 10-20, 20-30, ..., 90-100, Others

I need to count the number of users in each score member, BUT I need first to perform average score for each user and only then count how many users fall in each score range. this is for histogram display. Problem is that the average for each user is something that depends on filters, for example - average of all rows that fall in a specific month; obviously this average cannot be calculated in advance since there are many filters options that it depends on.

I only know how to perform the simple distint count, meaning - count number of users in each range; however, if a user has 2 scores, one 0 and one 100, I want him to be counted for the 50-60 range member; in the below query, he is counted twice, once for the 0-10 and once for the 90-100.

This is the MDX query:

SELECT
{
[Measures].[M1]
}ON COLUMNS,
([SRange].[All]).CHILDREN ON ROWS
from cube1

Measure M1 is implememnted as "<AggregateFunction>DistinctCount</AggregateFunction>" over the UserId column of the above fact table.

Thanks.
Post Reply