I was working with a large manufacturer when I faced an interesting business requirements.  The client stated that they had a lot of products with a lot of features available in one or more products.  For example: the same model car may come with an MP3 player or without one.   The end user would select the desired features when configuring the product.  The client wanted to do analysis on an ad-hoc selected combination of features.  The challenge was to treat the selection as a group in an “all-or-nothing” fashion.  In other words, either all features were present or the product should not be selected.  For example: Product 1 might have only Feature 1 while Product 2 had Feature 1 and Feature 2.   For the analysis, if both Future 1 and Feature 2 are selected then ONLY Product 2 should be selected.

SSAS handles by default the selection using a logical “OR”. In the previous example, both products will be displayed since both of them contain Feature 1.   The resulting matrix from cross joining the products with the features generated in unusable number of records, even after applying the “Feature” filter.  This was an acceptable solution.  The client wanted to see ONLY the products, which had all the selected features.   In technical specs, a logical “AND” must be applied on the “Features” filter.  

I came with a solution using the “Existing” function to detect what features were selected in the filter and then count them.  The products and features dimensions were joined thru a regular relationship to a measure group called “ProductFeatures”.  The measure group contained “Feature Count” measure, which did simple count of how many features were in a given product.   If the feature count and the “existing” count were the SAME, then the product should be displayed.  So the MDX prototype was “with member [Current Feature Count] as iif(count(existing [Features]) = [Measures].[Feature Count] , [Measures].[Feature Count], NULL) select non empty [Current Feature Count] on 0, [Products] on 1 from [Features]” and worked fine in most cases.

In some cases, the above solution does not work.   The reason for that was the difference in the behavior of the “Existing” function.  If the “Feature” filter selection was placed in the “WHERE” clause of the MDX statement, then it worked as expected.  BUT if the filter selection was placed in the sub-cube clause, then the “Existing” function returned all feature members (unless the Feature dimension was placed on one of the Axis).  That made no sense but there was nothing to be done to change the “Existing” behavior.

Here are some examples of the “Existing” function behavior:

a)  “select…from…where ({Feature1,Feature2})” – this works fines with a count of two (out of 10 total features) – since the “feature” dimension is the “WHERE” clause (i.e. one of the Axis).
b) “select {Feature1,Feature2} on 0 from…” – this works fines with a count of two (out of 10 total features) since the “feature” dimension is selected on one of the Axis.
c) “select…from (select {Feature1,Feature2} on 0 from….)” – this does NOT work and comes with a count of 10 (out of 10 total features) since the “feature” dimension is NOT placed on one of the Axis.

Again, the solution with counting the existing members works fine for most cases.  If the client software generates MDX with a “sub-select/sub-cube/” then the above solution will NOT work due to the misbehaving “Existing” function. 

I took it as a challenge to make the solution suitable for all client tools (including filters in the sub-select/sub-cube).   I did some digging and found that ASSPs were needed to detect what members were selected in the “sub-select/sub-cube”; basically fix the “Existing” behavior issues.  I added some new functions to the BI Portal SSAS DLL and was able to implement the solution with success.  Yes, it’s possible but it’s not an easy task.  Here is a snapshot of my end result: