To make a long story short
If you use an other measure in a calculated measure, you better remember to include it in your perspective – otherwise you always get a big fat nothing as result
The long story
At a certain client of ours we had implemented a unique count measure by counting the number of corresponding attribute members in a related dimension.
The fact would be structured this way (PatientExpandedFact):
DateKey PatientKey Admitted Wating …
20120101 10101010 1 0
20120102 10101010 1 1
20120103 10101010 1 0
And PatientKey would point to a dimension (PatientDim):
The MDX for the calculated measure would be someting like this
Unique Patients:= Iif( Count( nonempty(existing([PatientDim].[SocialSecId].members ) ,[Measures].[Count PatientExpandedFact]) )>0 ,Count( nonempty( existing( [PatientDim].[SocialSecId].members ), [Measures].[Count PatientExpandedFact] ) )-1 ,NULL )
This approach for calculating unique counts has pretty good performance compared to the “normal” DistinctCount in SSAS and it worked perfectly – UNTIL!
UNTIL, something like springcleaing hit the cube. [Measures].[Count PatientExpandedFact] was visible in the cube and available in the perspective the users connected to which it was not supposed to be. So we made the measure hidden and removed the check mark marking its availability in the perspective. Oh boy, we should never have done that, mistake # 1:-)
Our second mistake was not to check the measure right away. It was only after the deployment a week later or so, that the client noticed that the measure returned nothing in Excel.
After several hours of tiresome experiments ,bug tracking, googling and using plain mdx in ssms did it occur to us, that it was related to the perspective.