MDX Query Returning Hierarchy is defined in axis already

The question is why is this an error? The [Collective] dimension appears on both the ROWS axis and the slicer axis. I am not so good with MDX as to be able to give you a working version of the query off the top of my head and I only have a couple of minutes this morning, but I think the [Collective] portion of the WHERE needs to move to ROWS.

For the [Collective].[Merchant Name] members in WHERE I think they need to become a set that replaces [Collective].[Children] on ROWS. I think the [Collective].[Active].&[1] can be done with EXISTS({<[Collective].[Merchant Name] Set>}, [Collective].[Active].&[1]) -- that would become the ROWS set.
 

You should move [Collective] members from slicer (WHERE) to rows axis.

best regards

If you move [Collective].[Active].&[1] to ROWS then it will become part of the output.

Actually, for this type of query that doesn't require higher-level summary totals you can simply move your filters to sub-selects. The multi-axis restriction doesn't apply when the duplications occur across different subcubes. I was rushing yesterday, and most queries that I have been writing lately can't use subcubes because of the summary comparison values that they must calculate, and I overlooked the obvious. The redesigned query would look something like the code snippet below. (Note: I have not tried to test this query; it might contain syntax errors)
I have simply taken each filter set and moved it to its own sub-select of the form
(SELECT (filter set) ON COLUMNS FROM inner sub-select)
I have not included a WHERE clause in the main query. It is usually not necessary when using sub-selects. It can be re-introduced, if needed, for dimensions that do not appear on COLUMNS or ROWS in the main query.

SELECT  (UNION ({[KPI Metadata].[KPI Name].&[itemMatchedOffers]},
{[KPI Metadata].[KPI Name].&[TotalOffers]})         )  
ON COLUMNS,
 
non Empty
(
 [Collective].[Merchant Name].CHILDREN
) ON ROWS FROM 
	(SELECT 
	   ([Collective].[Merchant Name].[ Hiccups Childrens Boutique] ,
		[Collective].[Merchant Name].[1 Stop Camera] ,
		[Collective].[Merchant Name].[1 World Sarongs] ,
		[Collective].[Merchant Name].[101Phones.com] ,
		[Collective].[Merchant Name].[123Inkjets]) ON COLUMNS FROM
		   (SELECT ([Collective].[Active].&[1]) ON COLUMNS FROM 
		      (SELECT ([Source].[Source Name].&[DAProdExtract]) ON COLUMNS FROM
		         (SELECT ([Time].[Date].&[2010-04-15T00:00:00]) ON COLUMNS FROM [kpi]
		         )
		      )
		   )
		)
     )

Select allOpen in new window