I'm trying to create a Cubeset that filters on 2 columns, and excludes a certain value. I can get it to work fine if just filtering on a single column like:
=CUBESET("DataSource","EXCEPT(EXISTS([Data].[CustomerName].children, {[Data].[ResolvedByName].&["&$A$1&"]}),{[Data].[CustomerName].&})","",2,"[Measures].[Number of Tickets Resolved]")
This brings back a set of customer names ordered by Number of Tickets Resolved, excluding tickets that have a blank customer name, and only where the person resolving is equal to the value in cell A1.
I want to tweak this so it also filters where resolving month is equal to the value in cell B3. According to all the examples that I can find, it should be as simple as this:
=CUBESET("DataSource","EXCEPT(EXISTS([Data].[CustomerName].children, {[Data].[ResolvedByName].&["&$A$1&"], [Data].[ResolvedMonth].&["&B$3&"]}),{[Data].[CustomerName].&})","",2,"[Measures].[Number of Tickets Resolved]")
however this does not work and I can't figure out for the life of me why. Any ideas?
If you change the { and } characters to ( and ) it should work.
The problem is that the curly brackets indicate a set but a set must have members of the same hierarchy. The parenthesis indicates a tuple which has one member of different hierarchies.