Hi,

I have an MDX query that is returning NULL for all rows, because I have applied a filter that results in absolutely no nodes being found.

I need for this query to return all requested rows and columns with zeros (0) in each result cell.

I have tried CoalesceEmpty() and iif(IsEmpty(),0,...) and 0+[MeasureName].

I have even tried creating the measures as static values of zero, but I guess because the filter points to no nodes, even that returns nulls in every cell.

When I remove the culprit filter from the query, the results return fine.

Is there something I can do in MDX so that my query will always return 0's instead of nulls regardless of the f

Sure:

WITH 
	MEMBER [Sale Transaction Types].[By Sale Type].[OSF] AS 
		AGGREGATE({[Sale Transaction Types].[By Sale Type].[Sale Type].&[Cash]}*EXCEPT([Financial Institutes].[By Lender], [Financial Institutes].[By Lender].&[Cash]),[Measures].CurrentMember)
	MEMBER [Sale Transaction Types].[By Sale Type].[Cash] AS 
		AGGREGATE({[Sale Transaction Types].[By Sale Type].[Sale Type].&[Cash]}*{[Financial Institutes].[By Lender].&[Cash]},[Measures].CurrentMember)


	MEMBER [Measures].[GrossRevenue] AS AGGREGATE({
			[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[VehicleSale]
		,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[ProductSale]
		,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[VehicleSaleReversal]
		,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[ProductSaleReversal]
		,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[VehicleSaleDelivered]
	}, [Measures].[FI Gross])
	MEMBER [Measures].[VehicleCount] AS AGGREGATE({
			[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[VehicleSale]
		,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[ProductSale]
		,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[VehicleSaleReversal]
		,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[ProductSaleReversal]
		,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[VehicleSaleDelivered]
	}, [Measures].[Vehicle Sold Count])
	MEMBER [Measures].[CBAdj] AS AGGREGATE({
			[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[FIAdjustment]
		,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[FIChargeback]
	}, [Measures].[FI Gross])
	MEMBER [Measures].[GrossPVR] AS iif([Measures].[VehicleCount]=0,0,[Measures].[GrossRevenue]/[Measures].[VehicleCount])
	MEMBER [Measures].[NetPVR] AS iif([Measures].[VehicleCount]=0,0,([Measures].[GrossRevenue]-[Measures].[CBAdj])/[Measures].[VehicleCount])
	MEMBER [Measures].[GrossPVRx] AS 0
	MEMBER [Measures].[NetPVRx] AS 0
SELECT
{
	[Measures].[GrossPVRx]
,	[Measures].[NetPVRx]
} ON COLUMNS,
{
	[Sale Transaction Types].[By Sale Type].[Sale Type].&[Finance]
,	[Sale Transaction Types].[By Sale Type].[Sale Type].&[Lease]
,	[Sale Transaction Types].[By Sale Type].[Cash]
,	[Sale Transaction Types].[By Sale Type].[OSF]
} ON ROWS
FROM [MyCube]
WHERE ({[Organization].[Org Tree].&[2066]},{[Organization].[By Manufacturer].[Brand].&[060]},{	
		[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[VehicleSale]
	,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[VehicleSaleReversal]
	,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[ProductSale]
	,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[ProductSaleReversal]	
	,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[VehicleSaleDelivered]
	,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[FIAdjustment]
	,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[FIChargeback]
	,	[Sale Transaction Types].[By Transaction Type].[Sale Transaction Type].&[FrontEndAdjustment]
	},{[Time].[By Month].[Date].&[2013-01-01T00:00:00]:[Time].[By Month].[Date].&[2014-01-21T00:00:00]})

The underlined portion is at least partially the culprit.   If I take it out, the query returns results.

Update:   The query works if I take out either the underlined filter, or the one before it (

{[Organization].[Org Tree].&[2066]},

).   It doesn't work if I take out any of the other filters in the WHERE clause.

There are no rows in the fact table that have OrgTree member 2066 and Brand 060.  

Hi,

Can you please post the MDX filter query?

Thanks, Madhu

Hi Alleman,

Thank you for your question. 

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

Regards,
Charlie Liao

If you have any feedback on our support, please click here.

Hi,

If there are no records associated with OrgTree 2066 and Brand 060 then the mdx will not return any results. 

As per you query and description, the combination of OrgTree 2066 and Brand 060 are not returning the results it seems. If so, this is not the MDX issue it will be data issue.

Thanks, Madhu