How to replace null with zero in MDX

balanarasimha c 466 Reputation points

Hi Team,
How to replace null values with zero in MDX.
Am using bellow query to get results

SELECT [Order Date].[Fiscal Year].[Fiscal Year] ON COLUMNS,
{[Measures].[Sales Amount],[Measures].[Total Product Cost]
,[Measures].[Profit]} ON ROWS
FROM [Cube_AW];

And results are bellow


SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,103 questions
0 comments No comments
{count} votes

Accepted answer
  1. CarrinWu-MSFT 6,811 Reputation points

    Hi @balanarasimha c ,

    Thanks for your posting.

    Please try below MDX query:

    member [Measures].[ConverNullToZore_TotalSalesAmount] as  
    CoalesceEmpty([Measures].[Total Sales Amount],0)  
    member [Measures].[ConverNullToZore_TotalProductCost] as  
    CoalesceEmpty([Measures].[Total Product Cost],0)  
    	{[Measures].[ConverNullToZore_TotalSalesAmount], [Measures].[ConverNullToZore_TotalProductCost]}  
    on 0,  
    	[Due Date].[Date].members   
    on 1  
    from [Analysis Services Tutorial]  


    Best regards,

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 29,121 Reputation points

    For this you can use the CoalesceEmpty (MDX) function in MDX.

    0 comments No comments