How to replace null with zero in MDX

Question

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

Answer 1

Hi @Bala Narasimha Challa ,

Thanks for your posting.

Please try below MDX query:

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

Best regards,
Carrin

---

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.

Answer 2

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