Showing results for 
Search instead for 
Did you mean: 
Feilin Regular Visitor
Regular Visitor

MDX nonempty and not 0

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


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.





Community Support Team
Community Support Team

Re: MDX nonempty and not 0

Hi @Feilin,


As this is the forum supports for Power BI and DAX, for more professional advice to MDX, please post question on SSAS forum



Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.