Sql-server – Dimension Member as Calculated Measure in MDX

mdxolapsql server

I need to get a dimension member returned as a calculated measure.

Given:

Dimensions

  • Customer {ACME, EMCA, Universal Imports, Universal Exports}
  • Salesperson {Bob, Fred, Mary, Joe}
  • Credit Type {Director, Manager}

Measures

  • Credited Value
  • Value

Relationships

  • The Customer is a dimension of the facts that contain Value
  • The Customer, Salesperson and Credit Type are dimensions of the facts that contain Credited Value

I am trying to do the following:

Create calculated measures that will return the Salesperson with the largest $s credited in a role for a customer. e.g.

| Customer          | Director | Manager | Value |
|-------------------|----------|---------|-------|
| ACME              | Bob      | Fred    | 500   |
| EMCA              | Bob      | Fred    | 540   |
| Universal Imports | Mary     | Joe     | 1000  |
| Universal Exports | Mary     | Fred    | 33    |
  • ACME has Bob credited with 490 as Director
  • ACME has Fred credited with 500 as Manager
  • ACME has Mary credited with 10 as Director

I would like to use this as a calculated measure that I can use in any case where Customers are the ROW.

Best Answer

If I understand your problem correctly, something along this line should do the trick (of course you'd have to use the proper level, hierarchy and cube names):

   with
    member [Measures].[DirectorTemp] as topcount([Salesperson].[Salesperson].members,1,([Measures].[Credited Value],[Credit Type].[Director],[Customer].currentmember)).item(0).properties("Caption")   
    member [Measures].[Director] as iif([Measures].[DirectorTemp] = [Salesperson].UnknownMember.properties("caption"), null, [Measures].[DirectorTemp])
    member [Measures].[ManagerTemp] as topcount([Salesperson].[Salesperson].members,1,([Measures].[Credited Value],[Credit Type].[Manager],[Customer].currentmember)).item(0).properties("Caption") 
    member [Measures].[Manager] as iif([Measures].[ManagerTemp] = [Salesperson].UnknownMember.properties("caption"), null, [Measures].[ManagerTemp])


    select
        {[Measures].[Director],[Measures].[Manager],[Measures].[Value]} on 0,
        {[Customer].members} on 1
    from MyCube
Related Topic