Inclusive and Exclusive filter conditions - Filtered Measure Solution

[intapiuser]

The following article describes how to answer the following question: 
Which countries have sales for product A but not for product B

The solution is based on using the Filtered Measure plugin.

1. Go over the instructions in the Technical Details of the plugin.
Please follow steps 1-8.

2. After understanding how to work with the plugin, In our example, we would like to know what is the Cost for countries that had sales in particular Categories but didn't have sales in other categories.
Here are the selected fields for this business question:

1. In the EC we will duplicate the Category field
2. On the dashboard we will create 2 filters out of both Category fields - Include Category and Exclude Category
3. Add a new pivot table and add 2 Rows: Country and CountryID
   * The countryID field is presented only for QA purposes

3. We will create 4 Values in the Pivot table:

* After creating values #1 and #2 save them as Starred Formula to be then used in value #3

4. Filter Value #3 to include only values = 1
 

 5. Hide the first 3 values and leave only value #4 visible

6. We can now interact with the two filters on the dashboard level and see how the countries presented are changing accordingly.

 
If we were to add other filters, for example: Date year = 2013, we would be answering the business question of: 
What was the Cost in 2013 for Countries that in 2013 had costs for product A but not for product B

 The SQL equivalent of this business question is:

select c.[Country ID], sum(c.[Cost]) [Total Cost]
from [Commerce] c
where c.[Country ID] in (select c.[Country ID]
                            from [Commerce] c
                            join [Category] ca
                            on c.[Category ID]=ca.[Category ID]
                            where ca.[Category]='Apple Mac Desktops'
                            and getyear(c.[Date])=2013
                            group by c.[Country ID])                          

and c.[Country ID] not in (select c.[Country ID]
                            from [Commerce] c
                            join [Category] ca
                            on c.[Category ID]=ca.[Category ID]
                            where ca.[Category]='Camcorders'
                            and getyear(c.[Date])=2013
                            group by c.[Country ID])
and getyear(c.[Date])=2013
group by c.[Country ID]

That's it!