Today I started seeing some weird results when I was using NonEmpty function. If I just used NonEmpty without a filtering measure like
then I wasn’t getting any rows returned. But if I put a filtering measure in there like
Nonempty([Time].[Time].[Time],[Measures].[MyMeasure]) then I was getting data back. It occured to me that I had not even thought about what my default measure was. So I ran the following query
Select measures.defaultmember on 0
To my surprise it was a measure that would not really be applicable to most of my calculations. I was a little perplexed at why SSAS chose that measure as my default, so after some "Binging", I found this excerpt in one of Mosha’s old SSAS site pages
"For the default measure, the first non-hidden measure is chosen. If all measures are hidden, then the first measure is chosen. “First” here refers to the first measure in the measures collection in AMO and DDL (but not in MDSCHEMA_MEASURES, which is always alphabetically sorted). "
So I promptly changed the default measure to something that would have more meaning to the calculations I was writing.
To change the default measure, just simply put the following code in the bottom of your MDX script
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER= <whatever measure you want to use>