[SOLVED] MDX query works but ignores the EXCEPT clause

MDX query works but ignores the EXCEPT clause

I have been working on a custom dll (that is called via a custom xll / Excel Addin) to construct MDX and return 2D data.

It's working nicely and I just went to work out how I add the ability to send in an exclusion list using EXCEPT.

I built up a query with filtering and this query works except it ignores the EXCEPT. Anyone with more MDX than me (I'm about 2 months in haha :)) know why?

Thanks

Leigh

WITH  
Member  [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION 
Member  [Measures].[Isin_Label] AS [Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION 
SELECT 
NON EMPTY 
{[Measures].[Book_Label],[Measures].[Isin_Label],[Measures].[Notional.SUM]} 
ON COLUMNS, 
NON EMPTY ORDER
(
EXCEPT(
FILTER(
([Book].CHILDREN,[Isin].CHILDREN), 
([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION = "ALGO1")
), 
[Isin].[Isin].[DE0001104776]),
[Notional.SUM]
,
BASC) 
ON ROWS 
FROM[TraderCube] 
WHERE ([Date].[Date].[2019-11-18])

Solution

I returned to trying out combining my currently working 1..n FILTER builder in conjunction with an EXCEPT (requested by business). Unfortunately, despite the query passing syntax check and executing, as reported in original post the cube/server ignores it.

I just tried adding a <> to my FILTER and it worked! :)

Here's an example.

WITH  
  Member  [Measures].[Book_Label] AS [Book].[Book].CURRENTMEMBER.MEMBER_CAPTION 
  Member  [Measures].[Isin_Label] AS [Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION 
SELECT 
NON EMPTY {[Measures].[Book_Label],[Measures].[Isin_Label],[Measures].[Notional.SUM]} 
ON COLUMNS, 
NON EMPTY 
ORDER( 
FILTER(
([Book].CHILDREN,[Isin].CHILDREN), 
(([Book].[Book].CURRENTMEMBER.MEMBER_CAPTION = \"ALGO1\") AND 
([Isin].[Isin].CURRENTMEMBER.MEMBER_CAPTION <> \"DE0001102309\"))
),[Notional.SUM],
BASC) 
ON ROWS 
FROM[TraderCube] 
WHERE([Date].[Date].[2019-11-21])