CODEWITHSUNDEEP
mdxsaasolap-cube
user330612
user330612

Reputation: 2239

How to calculate average on two dimensions in MDX

I'm trying to convert the following SQL query into a calculated member in my SSAS cube.

SELECT ActionKey, AVG(1.0 * Days) AS AverageDays
FROM( SELECT ActionKey, UserKey, COUNT(DISTINCT DateKey) AS Days
        FROM [TEST].[dbo].[FactActivity]
        GROUP BY ActionKey, UserKey) a
        GROUP BY ActionKey

How do I do this in MDX? I tried the following but it's giving me wrong result

IIF([Measures].[Dim User Count] = 0, 0 , [Measures].[Dim Date Count]/[Measures].[Dim User Count])

In my cube, I have two derived measures . "Dim Date Count" which is count of rows in DimDate table and "Dim User Count" which is count of row of DimUser table. Both have many-many relationship with other dimensions of the cube, so i can calculate the distinct days and users easily.

Upvotes: 0

Views: 1193

Answers (2)

user330612
user330612

Reputation: 2239

This worked

AVG([Users].[User Key].[User Key], [Measures].[DATE COUNT])

Upvotes: 1

whytheq
whytheq

Reputation: 35557

(not a solution but maybe helps)

Are the two measures that you've created giving the results you expect? If you run the equivalent of the following against [YourCube] is it just the new measure [Measures].[AverageDays] that is wrong? 

SELECT 
  NON EMPTY 
    {
      [Measures].[AverageDays]
     ,[Measures].[Dim Date Count]
     ,[Measures].[Dim User Count]
    } ON COLUMNS
 ,NON EMPTY 
    {
        [Action].[Action].MEMBERS
      * 
        [Date].[Calendar].[Month].ALLMEMBERS
    } ON ROWS
FROM  [YourCube];

Upvotes: 0

Related Questions