cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

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.

 

 

 

 

1 REPLY 1
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

 

Thanks,

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.