# How to supress rows with 0 values. All measures, fact and calculated, are 0

• ### Question

• The cube going into browser or Excel, shows the following.

Measure                     Measure                 Calculated

TotalRevenue(measure)  TotalCost(measure)  GrossMargin(calculated)        Gross Margin%(calculated)

\$#,##0;(\$#,##0)         \$#,##0;(\$#,##0)   "\$#,##0;(\$#,##0)"              "#,##0.0 %;(#,##0.0 %)"

The excel gives me..

A   \$552,198                      (\$437,190)                            \$115,008                                              20.8%

B             \$0                                  \$0                                       \$0

How can I suppress/hide the row with \$0 values and NULL.  So the user does not need to filter, in Excel.

MariaLuz Munoz

Thursday, July 2, 2015 11:46 PM

• If it is a calculation, simply do a test like IIF(Measures.MyValue<>0, Measures.MyValue, null)

If it is a physical measure, then you have the option of converting nulls in the fact table to null. Arithmetically, null=0 in the cube. So, the aggregations will be equivalent, but a count(), avg() etc will be different. The NullProcessing property on the measure is important. Preserve, will keep it as null, while NullOrZero will convert a null fact measure to zero.

I like translating zeroes to nulls in the fact table, as this makes the cube a lot smaller. Just check that a zero is equivalent to a null. Ie, you don't need to count values including zeroes but excluding nulls.

Hope that helps,

Richard

Friday, July 3, 2015 3:38 AM

### All replies

• If it is a calculation, simply do a test like IIF(Measures.MyValue<>0, Measures.MyValue, null)

If it is a physical measure, then you have the option of converting nulls in the fact table to null. Arithmetically, null=0 in the cube. So, the aggregations will be equivalent, but a count(), avg() etc will be different. The NullProcessing property on the measure is important. Preserve, will keep it as null, while NullOrZero will convert a null fact measure to zero.

I like translating zeroes to nulls in the fact table, as this makes the cube a lot smaller. Just check that a zero is equivalent to a null. Ie, you don't need to count values including zeroes but excluding nulls.

Hope that helps,

Richard

Friday, July 3, 2015 3:38 AM
• Hi Maria,

I think we can not hide or suppress the entire row. If you don't want these records restrict in the DSV itself. Because there may be chance of some of the columns are  having \$0 but we have valid amounts an another column.

Thanks and Regards Rajesh

Friday, July 3, 2015 5:26 AM
• Hi Maria,

According to your description, you want to hide the records with 0 or null values. Right?

In Analysis Services, to hide these 0 value records is filtering the tuple(using the dimension members slicing the measures). We can only achieve this on MDX query level. Please use NON EMPTY() on the tuple level like:

NONEMPTY([DIM].[DIM].members,[Measures].[Measure])

MDX : Non Empty v/s NonEmpty

Regards,

Simon Hou
TechNet Community Support

Sunday, July 5, 2015 1:37 PM
• Richard, Thanks for the reply.

I have followed your suggestion to get the physical and calculated measure to NULL when these are 0. I'm still testing, and so far so good.

Monday, July 6, 2015 7:07 AM
• Maria,

I am also facing the same problem. I would like to hide entire rows | Column if the rows | columns hahing Zeros or nulls.Can you please reply.

Thanks,

Narendra

Monday, August 24, 2020 12:29 PM