Set Analysis with an Intersection

Every once in a while I come across a tip that makes me want to want to punch myself  break something  say ‘ah shucks’ because it’s so easy and it’s always bugged me.
We all know how to use set analysis to force charts to do what we want. In this case, I wanted to show four lines of sales over time:

It’s simply four expressions:

    Sum ({$<[Partner Type] = {'DRC'} >} [Sales Amount])
    Sum ({$<[Partner Type] = {'Major National'} >} [Sales Amount])
    Sum ({$<[Partner Type] = {'Regional'} >} [Sales Amount])
    Sum ({$<[Partner Type] = {'None Specified'} >} [Sales Amount])

Simple, right? The problem is that if you want to isolate one of the lines by selecting an Partner Type in a list box, nothing happens. That makes sense, because you explicitly told it in your set analysis to look at those four options. But it’s not really what you want to happen.

Fortunately, the solution is simple. Put a ‘*’ before each ‘=’.


    Sum ({$<[Partner Type] *= {'DRC'} >} [Sales Amount])
    Sum ({$<[Partner Type] *= {'Major National'} >} [Sales Amount])
    Sum ({$<[Partner Type] *= {'Regional'} >} [Sales Amount])
    Sum ({$<[Partner Type] *= {'None Specified'} >} [Sales Amount])


Now, when you choose a selection in your list box it works like expected.

As a bonus, you could skip the ‘*’ in one of your expressions. Let’s say that ‘None Specified’ is a baseline that you want to compare other partner types to. Change your expressions to:


    Sum ({$<[Partner Type] *= {'DRC'} >} [Sales Amount])
    Sum ({$<[Partner Type] *= {'Major National'} >} [Sales Amount])
    Sum ({$<[Partner Type] *= {'Regional'} >} [Sales Amount])
    Sum ({$<[Partner Type] *= {'None Specified'} >} [Sales Amount])


Now, when you make selections in your list box the ‘None Specified’ line stays put.

And now you know the rest of the story.