How to create SSAS calculated member with filtering using certain dimensional values?

• boxta

  Ten Centuries

  Points: 1374

  More actions

  July 23, 2012 at 7:02 pm

  #143658

  So I have a finance related DW (dimensional) and SSAS cube. Each transaction is one row.

  In the cube, there's the usual calculated member such as Net = Gross - Tax

  Supposedly in a highly simplified world, there are 4 types of transacitons

  1) Coin - 1st payment

  2) Coin - Renewal

  3) Subscription - 1st payment

  4) Subscription - Renewal

  Each transaction there will be a typeID from 1-4

  The typeID is already linked to a dimension, and the users have been happy.

  Now the user wants to have a report that only shows type 1-3. So basically a new metric that's NET - SubscriptionRenewal

  Obviously the user can hack it in Excel using named sets and stuff, but lets assume they don't want that. So I need to create a MDX calculation that automatically ignores value coming from type 4.

  How do i write the MDX????

  thanks

• boxta

  Ten Centuries

  Points: 1374

  More actions

  July 24, 2012 at 3:25 pm

  #1517092

  anyone??

  also.... just to clarify... it's (for example) for transaction type ID 4 (business key)

  but underlying data in the fact / dimension table could be multiple PKs

  so it could be Type PK 6, 9, 14, 20 that all point to type ID 4, and we need to include all those

  thanks