Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MDX Distinct count of Model&Color in Year Dimenson

Status
Not open for further replies.

karbon

Programmer
Dec 4, 2003
29
TR
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...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top