Removing null values in MDX result

_{Wednesday, July 16, 2014 1:53 PM}

Hi,

Actly, I want to hide the highlighted columns. Also, the requirement is – we should consider only those columns which satisfies the below criteria.

1.        Atlease one of the highlighted columns should have a value.
2.        Do not consider the records where both the columns are null.

So, in the below example, except first row, everything should needed for consideration.

Please help me on this

Please find screenshot for more info.

Thanks,

Munna

_{Monday, July 21, 2014 3:10 AM ✅Answered}

Hi Munna,

According to your description, you want to remove the null values in MDX results.

In MDX we can use Non Empty function and NonEmpty function to remove the null values. However, the Non Empty function and NonEmpty function have different order of execution on tuples.

• Non Empty is prefixed before the sets defining the axes and is used for removing NULLs. It operator works on the top level of the query, which means sets defined at axis are generated first and then the tuple having NULL values are removed.
• The NonEmpty() returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set.

Here are some links about the difference between Non Empty and NonEmpty.
http://blogs.msdn.com/b/karang/archive/2011/11/16/mdx-nonempty-v-s-nonempty.aspx
http://www.ssas-info.com/analysis-services-articles/50-mdx/2196-mdx-non-empty-vs-nonempty

Hope this helps.

Regards,

Charlie Liao
TechNet Community Support