SQL Server Performance Forum – Threads Archive
MDX Distinct count of Model&Color in Year Dimenson
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 />
]]>