Distinct Count Measure Group not distinct in MDX script

i attempting run following mdx query determine distinct amount of transaction numbers set including 2 dimension attributes. (pos , concierge)  when run query, seem getting distinct amounts each added together.  if the same transaction number exists in both, they both counted.  if attempt to get the same result using excel pivot table, desired transaction number count.  me getting result in mdx query?

with  set [week]	as strtomember("[date].[hierarchy - fiscal].[fiscal week].&[2012]&[48]")  set [store]	as {[financial transaction channel].[transaction channel desc].&[pos], 			[financial transaction channel].[transaction channel desc].&[concierge]}   												 member [store transactions returns] as
 	sum([week] * [store], [measures].[operational transactions count])   select { 	[store transactions returns] } on columns, { 	[week] } on rows edw
results: 		store transactions returns
wk. 48 - 2012 			78,636

the same attempt using excel pivot table yields this:

row labels	operational   transactions count
year 2012	78,368
half 2 - 2012	78,368
qtr. 4 - 2012	78,368
december - 2012	78,368
wk. 48 - 2012	78,368	78,636
1 stores	78,368
concierge	3,668
pos	74,968
grand   total	78,368

if add pos , concierge amounts, 78,636.  same result getting mdx query, after went through transactions line line, able determine 78,368 correct amount of distinct transaction numbers.

any on appreciated.

hi eric ,

i expect see duplicate member, since measure [measures].[operational transactions count] evaluated , sum members ([week] * [store]) include retured transactions .

my recommendation create new measure group 1 member distinct count transactions, or try code below :

with set [week]	as strtomember("[date].[hierarchy - fiscal].[fiscal week].&[2012]&[48]") member newmeasure distinctcount ([operational transactions dim].[transactions].[transactions].members) member sumit sum ( newmeasure ) select { 	newmeasure } on columns, { 	[week] } on rows edw {[financial transaction channel].[transaction channel desc].&[pos], 			[financial transaction channel].[transaction channel desc].&[concierge]} 			

hope handy ..

---

regards, david .

SQL Server  >  SQL Server Analysis Services