CODEWITHSUNDEEP
sql-server-2008mdxdata-warehouseolapcube
kyooryu
kyooryu

Reputation: 1509

Calculated measures in MSSQL Server 2008 BDS cube

I want to define 3 calculated members. I have a cube based on 2 tables, TrackInfo and Chart Positions. Chart Positions table consists of 36 week columns, which contain given track's position on top 100 list in given week (or 0 if the song didn't make it on the list):

[Entry ID] FOREIGN KEY,
[1st Week] FLOAT,
[2nd Week] FLOAT,

and so on, until week 36.

I'd like to calculate the following measures:

1) The amount of weeks the song has been in top 10

2) The amount of weeks the song has been in top 20

3) "Popularity meter", which would be done by the formula:

1 / ((average of all nonzero positions) * (37 - (weeks featured on List)))

Can anyone help me with those?

Upvotes: 0

Views: 319

Answers (1)

Marc Polizzi
Marc Polizzi

Reputation: 9375

A bit difficult without the cube definition to write some MDX but for 1) I'll take as example a similar question - number of year [Tokyo] has been in the Top 3 selling city :

 select
  [Measures].[Sales] on 0,

  Filter( 

    Generate( [Time].[Year].[Year].members as s1, 
             TopCount( s1.currentMember * [Customers].[City].members, 3, [Measures].[Sales] ) 
    ) as s2,

    IIF( s2.current(1) IS [Customers].[Geography].[City].[Tokyo] , true, false )

  )

  on 1 

  from [Sales]

This works with a demo cube available in icCube; should be the same with AS as this is quite standard MDX. I think you'll get the idea with the Generate function. Then you can create a calculated measure that will Count() the filtered set instead of putting this set on the axis (for the demo purpose).

Upvotes: 2

Related Questions