Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Analysis Server and Reporting Services (2005)  MDX Question - getting distinct list of members

Author  Topic  ac_786 Starting Member 2 Posts Posted - 2010-04-28 : 16:07:27 I have a SSAS 2005 cube with 5 dimensions. It has real estate data (Properties, Loans, etc.). The Property dimension has a attribute called MeasurementType with values Square feet, Units, Rooms, etc. The Loans dimension has different loans. There is a measure group that tracks Properties operating data, e.g. Revenue, Operating expenses, etc. I have calculated measures that give me the RevenuePerUOM (UOM - Unit of Measurement; e.g. Sq. ft., units, etc). The issue is that the RevenuePerUOM does gives values even when there are different Measurement Types (e.g. Some properties with Sq. Ft. and some with Rooms are all averaged). My Calc is very simple: RevenuePerUOMCalc = IIF([Measures].[Per UOM Denominator]=0,0, [Measures].[Revenue] / [Measures].[Per UOM Denominator]) I would like to add some logic to it which says that if the current data being displayed corresponds to more than one distinct Measurement Type return a blank or zero. Only do the calc. if the user is looking at a single Measurement TYpe. How do I do that? Thanks!