Most of the time, selecting items in a PivotTable in a PowerPivot or SSAS 2012 Tabular model works in the way you want: if you select more than one item from a column it acts as an OR filter on that column; selecting items on another column acts as an AND filter on the selection. Consider a simple PowerPivot model built from the following Excel tables (where the only relationship is on the FruitKey columns):

With a measure that sums up the Sales column, such as:

Sum of Sales:=SUM(SalesFact[Sales])

You can built a PivotTable like the one below that shows the sales of Citrus fruit that are Orange or Yellow (Citrus=Yes and (Colour=Orange OR Colour=Yellow):

So far so good. What happens if you want to see the sales of fruit that are either Citrus OR Yellow? If you know the selection in advance, or you have control over how your DAX is generated, you can build an expression like this measure that does the job:

Sum of Sales Citrus or Yellow:=
CALCULATE(
  SUM(SalesFact[Sales])
  , FILTER(ALL(Fruit1), Fruit1[Citrus]="Yes" || Fruit1[Colour]="Yellow")
)

But what if you don’t want to hard-code the filter you’re using, and want to let your users control what they are ORing? Here’s how…

First of all, you need to have two identical tables in your model that allow the user to make selections for the two conditions they are ORing. Here’s an example with the data from above:

Here I’ve got two tables, Fruit1 and Fruit2, for controlling the OR selection; only Fruit1 has a relationship with SalesFact though. Next, you need a measure that will apply the OR filter. The way this needs to work is as follows: take the whole of the Fruit table and if the user has selected something on Fruit1 then allow those rows through, and if the user has selected something on Fruit2 then allow those rows through the filter as well. Here’s the final DAX measure:

OR Sales:=
IF(
  OR(ISCROSSFILTERED(Fruit1[FruitKey]), ISCROSSFILTERED(Fruit2[FruitKey]))
  ,CALCULATE(
    SUM(SalesFact[Sales])
    , FILTER(
      ALL(Fruit1)
      , IF(ISCROSSFILTERED(Fruit1[FruitKey]), CONTAINS(Fruit1, Fruit1[FruitKey], [FruitKey]), FALSE())
      ||
      IF(ISCROSSFILTERED(Fruit2[FruitKey]), CONTAINS(Fruit2, Fruit2[FruitKey], [FruitKey]), FALSE())
    )
  )
  , SUM(SalesFact[Sales])
)

The key points here are (starting from the inside of the expression and working outwards):

• I’m using ALL(Fruit1) to get all the rows from the Fruit1 table, regardless of what has been selected, and then passing that table to the FILTER() function
• I’m then using the ISCROSSFILTERED() function on the FruitKey columns on both tables to see if the user has selected anything from any columns on those tables; if they have, then the FruitKey column will be filtered in some way
• Then, if something has been selected on either table, in my filter of ALL(Fruit1) I’m allowing a row to pass through the filter if the value of FruitKey is present in the user-selections on Fruit1 or Fruit2. This preserves the original selection on Fruit1 and adds the selection on Fruit2 to it. It would be much easier to do this if there was a way of unioning table expressions in DAX!
• Finally, I’m using the resulting table in the CALCULATE() function to get the sum of Sales for that selection. If nothing has been selected, however, the outer IF() simply returns the sum of Sales Amount with none of this logic applied.

Here’s a PivotTable using this measure with Citrus from Fruit1 on rows and Colour from Fruit2 on columns:

And, if you don’t want this OR functionality, all you need to do is ignore the Fruit2 table. Here’s a PivotTable with Citrus and Colour from Fruit1 on rows and columns:

I’m not sure this is the most elegant solution to this problem… if I think of one I’ll blog about it, and if you can think of one then please leave a comment!

Simple time series calculations, such as comparing sales in the current year with the previous year, are easy to do in DAX and well-documented. What happens if, in PowerPivot or the SSAS 2012 Tabular model, you want to compare the sales in any two arbitrary time periods – for example you want to compare (and maybe find the growth from) sales in January last year and February this year?

There are several ways of solving this, but here’s one. First, you need two Date dimension tables, one for each of the two time periods you’re comparing. Using the Adventure Works DW database here’s an example of how this can be done:

In this case I loaded DimDate and FactInternetSales and created an active relationship between the two tables on OrderDateKey=DateKey. I then loaded DimDate again, called it DimDateCompare, and created another relationship between it and FactInternetSales on OrderDateKey=DateKey but set this second relationship as inactive. This means that any selection on DimDateCompare will be ignored unless the relationship between it and FactInternetSales is activated using the UseRelationship() function.

You can then create a measure to get the Sum of the SalesAmount column for the selection on DimDate, which is simply:

Sum of Sales Amount:=SUM([SalesAmount])

Then create another measure that uses the relationship between DimDateCompare and FactInternetSales and ignores any selections on DimDate (to do this, you just need to use the All() function):

Sum of Sales Amount for Comparison Period:=
CALCULATE(
    SUM([SalesAmount])
, ALL(DimDate)
, USERELATIONSHIP(FactInternetSales[OrderDateKey], DimDateCompare[DateKey]))

And finally create a measure to find the difference between these two measures:

Difference:=[Sum of Sales Amount] – [Sum of Sales Amount for Comparison Period]

To show how this works, look at the following PivotTable showing these three measures:

I have CalendarYear from DimDate on the rows of the PivotTable and CalendarYear from DimDateCompare on the Slicer. The Sum of Sales Amount measure shows, as you would expect, the sum of sales for each year on DimDate; the Sum of Sales Amount for Comparison Period ignores what’s on rows and returns the sum of sales for the two years selected on DimDateComparison, ie 2001 and 2002. This is £3,266,373.66 + £6,530,343.53 = £9,796,717.18; and Difference shows the second of these values subtracted from the first.

Not a very interesting calculation in itself perhaps, but I’m intrigued by the possibilities of having tables in the model that only have inactive relationships with other tables; up to now I’ve only used inactive relationships in scenarios where there have been another active relationship between the same two tables, for example as described in Kasper’s post here.