1

If i rename my measures and use COALESCEEMPTY this works perfectly but i need to maintain the real name of the measures. Does anybody know how to replace null values with 0 without having to rename the measures.

WITH 
    MEMBER [Measures].[col_] AS COALESCEEMPTY([Measures].[col],0)
    MEMBER [Measures].[col2_] AS COALESCEEMPTY([Measures].[col2],0) 
    MEMBER [Measures].[col3_] AS COALESCEEMPTY([Measures].[col3],0) 
Select 
    NON EMPTY {[Measures].[col_], [Measures].[col2_], [Measures].[col3_]} 
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS , 
    NON EMPTY Hierarchize({DrilldownLevel({[Region].[Region].[All]})}) 
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, MEMBER_TYPE ON ROWS FROM (
    SELECT ({[Category].[Category].&[Oil]}, {[Year].[Year].&[2019]}) 
ON COLUMNS FROM [SIIBPAGESATDIREKTEF]) CELL PROPERTIES VALUE
E.Lahu
  • 399
  • 3
  • 15

1 Answers1

2

There is two better ways to achieve your aim and without creating new measures:

  1. Use format_string property in the measure properties.

The FormatString property accepts four arguments separated by a semicolon (;). The first argument is how positive value should be formatted, the second is how negative values should be formatted, the third argument is how 0 values should be formatted, and the fourth argument is how NULL should be formatted. The fourth argument is the one we are interested in! Here is an example of how I am formatting the Reseller Sales Amount: “#,##0.00;-#,##0.00;;0”.

enter image description here

  1. use scoope statement in calculations tab :

    SCOPE([Measures].[col_]); THIS=IIF(ISEMPTY([Measures].[col_]),0,[Measures].[col_]); END SCOPE;

Visit : https://sqldusty.com/2014/07/15/how-to-display-0s-instead-of-null-in-your-ssas-cube-mdx-query-results/

Naro
  • 800
  • 6
  • 11