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
There is two better ways to achieve your aim and without creating new measures:
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”.
use scoope statement in calculations tab :
SCOPE([Measures].[col_]); THIS=IIF(ISEMPTY([Measures].[col_]),0,[Measures].[col_]); END SCOPE;