How do I find only the rows in MDX where I have multiple columns and I need to remove those which are "useless". "Useless is defined as BOTH of the columns being either null or 0?
For instance, of the following:
... Measure 1 Measure2
1 0
1 null
0 1
null 1
null 0
0 0
null null
0 null
I only want the first 4 rows because they have value (other than 0) in one of the columns, whereas the last 4 I don't want because they have no value or 0 as value.
So far, I tried the following, using nonempty
SELECT {
[Measures].[Measure1]
,
[Measures].[Measure2]
} ON COLUMNS,
NONEMPTY (
CROSSJOIN (
[Date].[Date]
,
[Some].[...]
,
[Other].[...]
,
[Dimensions].[...]
)
,
{
[Measures].[Measure1]
}
)
ON ROWS
FROM
...but that doesn't give me what I want, and only filters on one of the measures. I guess I should put an EXCEPT() for .&[0] somewhere, but I'm unsure of where and how to interact with the nonempty, to make sure I get what I want, and it's a pretty big database, so trial and error is something I'd really like to avoid at this point...
Any help or suggestions highly appreciated, thanks!
Also, I am not 100% sure I should be using the NONEMPTY() function as opposed to the NON EMPTY keyword, so feel free to give some advice on that as well.
