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 Distinct count of Model&Color in Year Dimenson

Author  Topic  karbon Starting Member 9 Posts Posted - 2007-01-30 : 02:31:18 Hi everyone. suppose that i have 3 dimensions as. ProductModel and Color and SalesFact table. i want to find the distinct count of ProductModel+Color. i mean. Year/ProductModel/Color/Sales --------------- 2006/010101 /White /1.0 2006/010101 /Black /2.0 2007/010101 /White /1.0 2006/010102 /White /1.0 2007/010102 /Black /3.0 2007/010103 /White /2.0 i am trying to add CalculatedMember in AS2000. i can easily count ProductModel with. member formula... filter(descendants([ProductModel ],[ProductModel ].[Model Id]),[Measures].[Sales]<>0).count) and i can get. By year and Model 2006 - 010101 - 2 2007 - 010101 - 1 2006 - 010102 - 1 2007 - 010102 - 1 2007 - 010103 - 1 But i want to get distinct count of ProductModel+Color by Year Dimension i mean. i want see. Year/ ModelCount/Model+colorCount 2006 - 2 - 3 2007 - 3 - 3 it means 2006 - 2(010101&010102) - 3 (010101White&010101Black&010102White) 2007 - 3(010101&010102&010103) - 3 (010101White&010102Black&010103White) i hope someone understand my question...