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..
Have you tried the BIDS to build such mDX query, it is achievable using that tool with a AS project. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
<br />Yes i need an <<expression>> to write in BIDS.<br /><<Calculations>> tab, <<New calculated member>> , <<expression>>..<br /><br />am i missing somethig?<br />is there another tool in BI Development Studio to build MDX queries?<br /><br />i just want to see distinctcount of 2 dimensions.<br />i should not be so difficult.<br /><br />DISTINCTCOUNT( «Set» ) ?????<br />i need an set expression<img src='/community/emoticons/emotion-6.gif' alt='' /><br /><br />