Comments And Descriptions In DAX

With my Technitrain hat on I’m sitting in on Marco’s Advanced DAX course in London today, and the question of comments in DAX came up – which reminded me that this is something I’ve been meaning to blog about. DAX as a language supports comments, but unfortunately it’s not possible to add comments inside a DAX measure or calculated column expression in either PowerPivot or SSAS Tabular right now (which is where they’re most needed – I hope this changes in the future). That said, there are some other things you can do to add textual explanations and descriptions to your DAX measure code.

Before we get onto the workarounds, a quick word about comments in DAX. These can only be used in DAX queries, and the types of comment supported are the same as in MDX: double-dashes and double-forward-slashes for single line comments, and forward-slash-asterisk to start a multi-line comment and asterisk-forward-slash to close a multi-line comment. Here’s an example:

--single line comment

//another single line comment

/*a multi-line

comment*/

evaluate table1

 

What can be done with measures though? After all, that’s where the most complex DAX is usually written.

First of all, you can add a description to a measure by right-clicking on it in the measure grid and selecting Description:

Unfortunately this description is not easily accessible to end users anywhere (it would be great if it appeared as a tooltip in a PivotTable, for example) but it can be seen in an Excel worksheet by running a DMV query. DMV queries can be run in Excel 2013 in the same way as DAX queries, using a query table as described here; the DMV query to use is:

select 

measure_name as [Measure Name], [description], measure_is_visible 

from $system.mdschema_measures

 

Unfortunately all hidden and implicit measures are returned, and even when the table is filtered so that only measure_is_visible=true there are still a lot of measures that probably shouldn’t be shown.

Similarly, descriptions can be added to any column (calculated or not) in your model, again by right-clicking on it and selecting Description.

This description can be displayed in the worksheet using the following DMV query:

select

hierarchy_name as [Column Name], [description] as [Description] 

from $system.mdschema_hierarchies

where cube_name='model'

 

You can also write text direct to cells in the measure grid too. When I first saw a customer do this I was worried that it might not be supported, but I’ve been told that it is; so long as you don’t use the =: used for defining measures then you should be ok.

This is probably the best way to add comments to your code, if only because it’s the most visible to anyone looking at your PowerPivot/SSAS Tabular model. Of course, for it to be effective you’ll need to have a system for arranging your measures in the measure grid; in “SQL Server Analysis Services 2012: The BISM Tabular Model”, Marco, Alberto and I recommended that you arrange all your measures in the top-left hand corner of the measure grid and I think that’s still a good idea, but the use of text in cells to create headings for groups of measures as well as descriptions can help a lot too.

UseRelationship() and Tabular Row Security

Quick summary: DAX measures in SSAS Tabular that use the UseRelationship() function return an error when row security is applied to a table. I’m surprised this hasn’t been documented somewhere – I know Marco came across it some time ago, but I ran into it again recently so I thought I’d mention it.

Consider the following simple SSAS Tabular model, based on Adventure Works DW:

There’s an active relationship between DateKey and OrderDateKey, and an inactive relationship between DateKey and ShipDateKey. The following measure returns the sum of Sales Amount and activates the inactive relationship:

Sales Amount by Ship Date:=
CALCULATE(SUM([SalesAmount]), USERELATIONSHIP(FactInternetSales[ShipDateKey], DimDate[DateKey]))

However, when there’s row-level security defined on the DimDate table (though not FactInternetSales) you will see an error for this measure when you browse the model:

ERROR – CALCULATION ABORTED: USERELATIONSHIP function cannot be used while querying table ‘FactInternetSales’ because of the row level security defined on table ‘DimDate’.

No workaround, I’m afraid, but this isn’t a bug, it’s a known limitation.

Cloud-Based DAX and MDX Formatter

It’s been away for a while, but now it’s back – the site where you can enter your MDX or DAX code and have it formatted for you! Here’s the link:

http://formatmdx.azurewebsites.net/

Thanks to Nick Medveditskov, the man responsible for it.

Dynamic DAX Query Tables in Excel 2013

PivotTables are all well and good, but sometimes when you’re building reports you just want a plain old list of things. Excel tables are perfect for this, and in Excel 2013 you can bind a table to the results of a static DAX query against the Excel Data Model. Unfortunately it’s not possible to make this query dynamic without a bit of VBA – so in this post I’ll show you how to do it.

Before I start, though, you may be thinking “What’s the point of this?”. After all, if you have too much data for the native Excel table functionality to handle, you can always use the Excel Data Model and make a PivotTable look just like a table, and when you do that you can use filters, slicers and so on to control what gets displayed. This is certainly a valid approach but the big disadvantage of a PivotTable is that it doesn’t always give you the best possible performance because of the way it generates its MDX, and because DAX queries are anyway faster than MDX queries for this kind of detail-level reporting. For large tables with lots of columns then a hand-rolled DAX query might give you significantly better performance than a PivotTable, as well as more control over the filtering logic.

Let’s look at a worked example…

Step 1: Import some data into a table

For my example, I have imported the DimDate table from the Adventure Works DW database in SQL Server into a table in Excel.

The key thing to remember at this point is to make sure you check the box to add the data to the Excel Data Model:

Step 2: Define a DAX query for this table

Kasper shows here how to use a static DAX query to populate a table in Excel, so I won’t repeat what he says. All I’ve done in my example is to change the table to use the following DAX query:

evaluate DimDate

…which returns the whole contents of the DimDate table, so in fact at this point the table looks exactly the same as it did before I made this change.

Step 3: Add some UI to allow the user to filter the data

Now I want the user to be able to filter this table in two ways:

1. By using a slicer to control which days of the week are displayed

2. By entering a value into a cell, and filtering the table so only the rows where the day number of the month is greater than that value

Here’s what this looks like:

I’ve also added a ‘Run Report’ button onto the worksheet for the user to press when they want to refresh the data in the query

Step 4: Use VBA to dynamically generate the query used by the table

The challenge is now to take the selection in the slicer and the value entered for the day number of month filter and use that to construct a DAX query.

Here’s an example of what one of these DAX queries might look like:

evaluate
Filter(
DimDate
, DimDate[DayNumberOfMonth]>21
&& (DimDate[EnglishDayNameOfWeek]=”Monday” || DimDate[EnglishDayNameOfWeek]=”Saturday”))
order by DimDate[DateKey]

Here I’m filtering the DimDate table so that the only rows displayed are where day number of month is greater than 21, and day name of week is either Monday or Saturday. If you’re interested in learning more about writing DAX queries, check out the series of blog posts I wrote on this topic here.

Paul te Braak has a great post here on how to work out what has been selected in a slicer using VBA, and I need to acknowledge the fact I’ve borrowed some of his code! Here’s my VBA routine, called by the button on the worksheet, to build and run the query:

Sub RunReport()

    Dim SC As SlicerCache
    Dim SI As SlicerItem

    Dim SelectedList As String
    Dim DayNumberOfMonthFilter As String

    Dim DAXQuery As String
    Dim DemoWorksheet As Worksheet

    Dim DAXTable As TableObject

    Set DemoWorksheet = Application.Worksheets("TableDemo")

    'Find the value of the cell containing the Day Number Of Month filter value
    DayNumberOfMonthFilter = DemoWorksheet.Range("DayNumberOfMonthFilter").Value

 
    'Find what is selected in the slicer Slicer_EnglishDayNameOfWeek

    Set SC = ActiveWorkbook.SlicerCaches("Slicer_EnglishDayNameOfWeek")
    SelectedList = ""

 
    'Loop through each item in the slicer and if it is selected

    'add it to a string that will be used in the filter condition
    For Each SI In SC.SlicerCacheLevels(1).SlicerItems

        If SI.Selected Then
            If SelectedList <> "" Then

                SelectedList = SelectedList & " || "
            End If

            SelectedList = SelectedList & "DimDate[EnglishDayNameOfWeek]=""" & SI.Caption & """"
        End If

    Next

    'Construct the DAX query
    DAXQuery = "evaluate Filter(DimDate, DimDate[DayNumberOfMonth]>" & DayNumberOfMonthFilter

    DAXQuery = DAXQuery & " && (" & SelectedList & ")) order by DimDate[DateKey]"

    'Bind the table to the DAX query
    Set DAXTable = DemoWorksheet.ListObjects("Table_DimDate").TableObject

    With DAXTable.WorkbookConnection.OLEDBConnection
        .CommandText = Array(DAXQuery)

        .CommandType = xlCmdDAX
    End With

 
    'Run the query

    ActiveWorkbook.Connections("ModelConnection_DimDate").Refresh

End Sub

 

And so there we go, a dynamic DAX table report in Excel 2013. If you’d like to download my example and check it out in detail, you can get hold of it here.

Building Relative Date Reports in PowerPivot

It’s a very common requirement when you’re building a report in PowerPivot (or indeed in any BI tool) for it to automatically show data for today’s date, the current week or month (plus maybe a set number of preceding days, weeks or months), without the user needing to change anything when they open the workbook. There are a number of ways of achieving this, but in this post I’m going to focus on one: building relative date columns in your Date dimension table. This stuff is by no means new and ground-breaking and I’ve seen this particular technique implemented many, many times, but it’s also something I get asked about fairly frequently and I can’t find any other blog posts detailing it so I thought I’d write it up.

To show how this works I’ve built a sample PowerPivot model in Excel. An important part of this sample model is a proper Date dimension table of course, and if you don’t have one in your data source there are plenty of ways of generating one automatically (Boyan Penev’s DateStream dataset in the Azure Marketplace, for instance, or this cool new Excel 2013 app I found today in the Office Store). Here’s the example I’ll be working with which has a Date dimension table and a Sales fact table with some values in it:

 

On the Date dimension table I’ve added four new columns, two to handle relative dates and two to handle relative months:

Relative Date Offset
=INT([Date] – TODAY())

Relative Month Offset
=((12 * YEAR([Date])) +  MONTH([Date])) – ((12 * YEAR(TODAY())) +  MONTH(TODAY()))

Relative Date
=IF([Relative Date Offset]=0
, "Today"
, "Today " & IF([Relative Date Offset]>0, "+", "") & [Relative Date Offset])

Relative Month
=IF([Relative Month Offset]=0
, "Current Month"
, "Current Month " & IF([Relative Month Offset]>0, "+", "") & [Relative Month Offset])

The first two of these columns contain integer values that are the number of days and months between today’s date and the date in the [Date] column on the dimension table. I’ve hidden these from client tools, and then then used them in the expressions for (and as the Sort Columns for) the next two columns which show the same values in a more human-readable form. Here’s what the results look like:

These new columns can be used in a variety of ways. For instance, I can now put my Sales measure in a PivotTable, put Relative Date in the Filter and select the ‘Today’ value, and then put Date on columns in the PivotTable and I’ll only see today’s date:

This is because, of course, selecting ‘Today’ on Relative Date automatically filters the [Date] column down to one value – today’s date (ie January 24 2013).

I can now also build reports that show data for the current month and previous month, without showing any dates at all:

There’s one final problem that needs to be solved though: the relative dates are calculated when the Date dimension is loaded and the calculated columns evaluated, but what happens tomorrow when the relative dates need recalculating? If I was building this solution in SSAS Tabular and reprocessing your model every night automatically then I wouldn’t have this issue; in PowerPivot I need to make sure I handle this. In Excel 2010 there’s no way to automate loading data into a table, alas, so the user would have to do the refresh manually alas. In Excel 2013 I can do this using VBA very easily, by putting the following code in the WorkBook_Open() event:

ActiveWorkbook.Model.ModelTables("Date").Refresh

Refreshing the Date table also automatically refreshes your PivotTables too, which is handy. This means that when I open the workbook tomorrow (ie January 25 2013), the relative dates will have shifted accordingly and my report will show data as of January 25 2013 and not January 24 2013.

You can download my Excel 2013 sample workbook here.

A Different Approach To Last-Ever Non-Empty in DAX

The post I wrote on the last-ever non-empty problem in MDX has been by far the most popular post I’ve ever written. It was the most popular post on my blog in 2012, and I wrote it in 2011! I hadn’t thought about how to solve the problem in DAX though, and when a few months ago Javier Guillen wrote an excellent post on exactly this subject I thought it wasn’t worth bothering with any more.

However, I changed my mind when was writing some DAX for a PowerPivot project recently and came across a totally different way to solve this problem which I thought I should write about. I’m not sure whether this approach is better or worse than Javier’s in terms of performance or maintainability, but it returns the same values as my original MDX solution and I’m sure those of you out there who like DAX would be interested in seeing it…

First of all, here’s the SSAS Tabular model I’m using for this post, which uses data from Adventure Works DW:

At the core of this approach is the idea that when you’re searching for the last non empty date on which a sale was made, all you need to do is this:

• Find the table of dates from the beginning of time up to the current date on your Date dimension table, then
• Find the last date from the date key column on your fact table (the column which joins onto the key column on your dimension table) in the context established by the table found in the previous step

Here’s a simple measure that illustrates this approach:

Last Ever Sales Date:=
CALCULATE(
LASTDATE(FactInternetSales[OrderDate])
, DATESBETWEEN(DimDate[FullDateAlternateKey], BLANK(), LASTDATE(DimDate[FullDateAlternateKey]))
,ALL(DimDate)
)

Running the following MDX query against the Tabular model (yes, I know, I’m using an MDX query, but wanted to have Customers on columns for testing purposes!) shows that it does indeed return the last ever non empty sales date:

SELECT
HEAD([DimCustomer].[Customer].[Customer].MEMBERS, 3)
*
{[Measures].[Sum of Sales Amount], [Measures].[Last Ever Sales Date]}
ON 0,
[DimDate].[FullDateAlternateKey].[FullDateAlternateKey].MEMBERS
ON 1
FROM [Model]

If we then want to return the value of [Sum of Sales Amount] for the last ever non empty date, then we just need to do this:

LENE Sales Date:=
CALCULATE([Sum of Sales Amount]
, CALCULATETABLE(
LASTDATE(FactInternetSales[OrderDate])
, DATESBETWEEN(DimDate[FullDateAlternateKey], BLANK(), LASTDATE(DimDate[FullDateAlternateKey]))
,ALL(DimDate))
, ALL(DIMDATE)
)

However, there’s a problem here: this measure works at the Date level, but it doesn’t return what you’d expect (or at least it isn’t consistent with my original MDX solution) at the year level. What happens if someone buys on two different dates in the same year? This code will still return the value of [Sum of Sales Amount] for the last sales date, not the value of [Sum of Sales Amount] for the last year that had a value. For example, take customer Carl A. She:

SELECT
{HEAD([DimCustomer].[Customer].[Customer].MEMBERS, 3)
,[DimCustomer].[Customer].&[Carl A. She]}
*
{[Measures].[Sum of Sales Amount], [Measures].[LENE Sales Date]}
ON 0,
[DimDate].[CalendarYear].[CalendarYear].MEMBERS
ON 1
FROM [Model]

Carl made two purchases on different dates in 2007, but this calculation returns only the value of the last purchase.

To get around this, I had to use some conditional logic. At the year level, instead of returning the sales for the last ever date that had a sale, what I actually want is the sales for all dates from the last ever date that had a sale back to the beginning of the year that contains that last date:

LENE Sales Year:=
CALCULATE(
[Sum of Sales Amount]
, CALCULATETABLE(
DATESYTD(FactInternetSales[OrderDate])
, DATESBETWEEN(DimDate[FullDateAlternateKey], BLANK(), LASTDATE(DimDate[FullDateAlternateKey]))
, ALL(DimDate))
, ALL(DimDate)
)

Here, instead of using LastDate, I’ve used DatesYTD to get that table of dates. The same problem happens at Month level too, so DatesMTD must be used:

LENE Sales Month:=
CALCULATE(
[Sum of Sales Amount]
, CALCULATETABLE(
DATESMTD(FactInternetSales[OrderDate])
, DATESBETWEEN(DimDate[FullDateAlternateKey], BLANK(), LASTDATE(DimDate[FullDateAlternateKey]))
, ALL(DimDate))
, ALL(DimDate)
)

Finally, a measure that detects whether we’re looking at a Year, Month or Date is necessary so the right value can be returned:

LENE Sales:=
IF(
HASONEVALUE(DimDate[FullDateAlternateKey])
, [LENE Sales Date]
, IF(HASONEVALUE(DimDate[EnglishMonthName])
, [LENE Sales Month]
, [LENE Sales Year])
)

Here’s a query, using a hierarchy on DimDate that contains Year, Month and Date levels, to show the results:

SELECT
{[DimCustomer].[Customer].&[Carl A. She]}
*
{[Measures].[Sum of Sales Amount], [Measures].[LENE Sales]}
ON 0,
[DimDate].[Calendar].MEMBERS
ON 1
FROM [Model]

I can’t help wondering whether there’s a better way to solve this problem of getting the Year, Month and Date values correct… but that’s a topic for a separate post I think.

Technitrain 2013 Course Schedule: SSAS, PowerPivot, MDX, SSIS, TSQL and SQL Server Engine Training in London

As you probably know, when I’m not blogging or wrestling with SSAS I run a small SQL Server training company here in the UK called Technitrain. If you’ve got some training budget to spare and London is convenient for you, you might want to check out the 2013 course schedule below:

• Introduction to StreamInsight with Allan Mitchell. London, 27-28 February http://www.technitrain.com/coursedetail.php?c=20&trackingcode=CWB
• Advanced TSQL with Dave Ballantyne. London, 19-20 March http://www.technitrain.com/coursedetail.php?c=19&trackingcode=CWB
• Introduction to PowerPivot with Chris Webb and Bob Phillips. London, 5-6 June http://www.technitrain.com/coursedetail.php?c=24&trackingcode=CWB
• Super-scaling SQL Server with Thomas Kejser. London, 13 June http://www.technitrain.com/coursedetail.php?c=21&trackingcode=CWB
• Real-world SQL Server Analysis Services Cube Design and Performance Tuning with Chris Webb. London, 18-19 June http://www.technitrain.com/coursedetail.php?c=23&trackingcode=CWB
• SQL Server Integration Services Design Patterns with Andy Leonard. London, 9-12 September http://www.technitrain.com/coursedetail.php?c=25&trackingcode=CWB
• Introduction to SQL Server Analysis Services 2012 Tabular Models with Chris Webb. London, 26-27 September http://www.technitrain.com/coursedetail.php?c=26&trackingcode=CWB
• Introduction to MDX with Chris Webb. London, 13-15 November http://www.technitrain.com/coursedetail.php?c=22&trackingcode=CWB

As you can see, I’m doing a fair bit of teaching myself but I’m also proud to have Thomas Kejser, Allan Mitchell, Dave Ballantyne, Bob Phillips and Andy Leonard teaching courses for me too. The aim is to deliver reasonably-priced, expert-level Microsoft BI and SQL Server training of the sort the regular training companies don’t provide; it’s basically the kind of training you’d get at a SQLBits or SQL Saturday pre-con but in a more traditional classroom environment and not limited to one day. If you’re reading my blog, you’re my target audience for these courses – so I hope to see you at one of them this year!

PowerPivot Top N Reports Using Excel Cube Formulas

Top N reports are an extremely common requirement: my customers are always trying to find their top 10 products or sales people or geographies by some measure or other. Luckily this type of report is fairly easy to build in PowerPivot if you’re using a PivotTable; in fact, Rob Collie wrote a good blog post on this subject only last week which is well worth a read. The problem with PivotTables is, however, that they are a pain to format and many people prefer to use Excel cube formulas for their dashboards – and unfortunately dynamic Top N reports are surprisingly difficult to implement with cube formulas. As the discussions here and here show, even when you’re using CubeSet and CubeRankedMember you need to know MDX pretty well and even then it’s a bit messy. Here, instead, is a pure DAX solution to the problem which, while not as simple as I’d like, involves no MDX, no clever use of Excel cube functions, and works when you select more than one item in a slicer.

The first thing you need to do is to create a table with as many rows in as you need items in your Top N report. In my example I’m going to return the top 10 products in a model built from the Adventure Works database, so here’s my table (called TopNRank):

Here’s my model in Diagram View:

And here’s a screenshot of my main worksheet, for reference, with two Slicers on CalendarYear and EnglishProductCategoryName; a PivotTable with a Top 10 filter applied on EnglishProductName (to check the output and for debugging); and below it my Excel formulas, with the ten values from the TopNRank table on rows and two measures called [TopN Product Name] and [TopN Product Sales] on columns, showing the same top 10 values:

Step 1 is to create a measure called [Sales] that simply sums up the values in the [Sales Amount] column:

Sales:=SUM([SalesAmount])

You can then create a measure, called [Product Rank] here (and shown in the PivotTable above), that returns the rank of each product by [Sales] for the current year and category:

Product Rank:=
IF(
  ISBLANK([Sales])
    , BLANK()
    , RANKX(ALL(DimProduct[EnglishProductName]), [Sales], [Sales], 0, Dense)
)

The basic idea for this approach is that with the Excel cube formulas, you’re going to use the values from the TopNRank table on rows and then use a measure to return the name of the top Nth Product for each row. This measure needs to return the name of the product that has the same rank value as whichever value from the TopNRank table is on rows. For example, in the screenshot above, in cell D21 there is a CubeMember function that returns the value 1 from TopNRank table; in cell D22 there is a CubeValue function that references the new measure, and this filters the list of all Products to return the name of the Product where [Product Rank] is 1, which is Road-150 Red, 48 (as you can see from the PivotTable).

There’s a problem with this approach, however, and that is that the RankX function always returns tied ranks when two products have the same value for [Sales]. So, in the PivotTable in the screenshot above, there are two products with the rank 2 because they have the same value for the [Sales] measure – and this causes big problems for the approach described in the previous paragraph. Despite what BOL says you can’t calculate a rank by more than one column, so the only way to get around this is to ensure that tied ranks can never occur, and the way I’ve done this is to rank by [Sales] and the name of the product by using the following measures:

Product Name:=
FIRSTNONBLANK(VALUES(DimProduct[EnglishProductName]), DimProduct[EnglishProductName])

Product Name Rank:=
IF(
  ISBLANK([Sales])
    , BLANK()
    , RANKX(ALL(DimProduct[EnglishProductName]),[Product Name])
)

Combined Rank:=
[Product Rank] + (1/[Product Name Rank])

Untied Product Rank:=
RANKX(ALL(DimProduct[EnglishProductName]), [Combined Rank],,1)

With this done, at long last it’s possible to create the measure that returns the name of the Top Nth product as follows:

TopN Product Name:=
IF(
  ISFILTERED(‘TopNRank’[TopNRank]) && ISBLANK([Sales])=FALSE()
    , FIRSTNONBLANK(
        FILTER(VALUES(DimProduct[EnglishProductName])
        , [Untied Product Rank]=VALUES(‘TopNRank’[TopNRank]))
    , DimProduct[EnglishProductName])
  , BLANK()
)

And here’s the measure that returns the value of [Sales] for each product:

TopN Product Sales:=
IF(
  ISFILTERED(‘TopNRank’[TopNRank]) && ISBLANK([Sales])=FALSE()
  , CALCULATE(
    SUM(FactInternetSales[SalesAmount])
    , FILTER(
      VALUES(DimProduct[EnglishProductName])
      , DimProduct[EnglishProductName]=[TopN Product Name]))
    , BLANK()
)

I’ve been told by the customer that implemented this approach that performance on larger models, while acceptable, is a bit slow and that it gets worse the more items you display in your top n list. This doesn’t surprise me and to be honest I’ll need to do some experiments to see if I can improve performance.

You can download my sample workbook (Excel 2010 64 bit, PowerPivot V2.0) from here.

OR Selections in DAX

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!

Comparing Any Two Time Periods in DAX

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.