Handling multiple date fields using multiple relationships

The previous part of this blog showed a problem - how to join two columns simultaneously to the calendar's Date column:

The problem: we can't join both columns using the same relationship.

One answer - shown in the previous part of this blog - is to import a second copy of the calendar table.  The other possible answer is to create two different relationships, and then specify within each measure which one we're using.

Creating the two relationships

Start by creating the first relationship:

Drag the DateBorn column (for example) onto the calendar Date column.

Now drag the DateJoined column onto the Date column to create a second relationship:

Create a second relationship, this time using the staff joining date, not the birth date.

SSAS shows the two relationships like this:

The one with the solid line is the active relationship.

The first relationship that you created is the active one (you can only have one active relationship between any given pair of tables), as double-clicking on it shows:

Double-clicking on the solid line shows that this is the active relationship.

The CALCULATETABLE function

The CALCULATETABLE function works in the same way as the CALCULATE function, except that it requires a table as the first argument rather than an expression: 

The CALCULATE function returns a value, whereas the CALCULATETABLE one returns a table of values.

The USERELATIONSHIP function

By default, when you refer to any expression which requires Analysis Services to look between tables, it will use the default relationship path to do this:

Where two or more relationships exist between a pair of tables, Analysis Services will use the one marked as active to connect them.

However, you can change this behaviour by using the USERELATIONSHIP function: 

Two measures to count staff

You can now create the following two measures:

Create the two measures shown in the staff table, using the formulae below.

Here's the measure to count the number of staff joined:

And here's the measure to count the number of staff born:

The only difference is the relationship they use to pick up on any calendar constraints from the calendar table.

The final pivot table

Using this method, you can't create the pretty diagonal effect as shown in the previous part of this blog, but you can compare the numbers born and joined for any year:

Again, the only tweak I've made is to sort the years into reverse order.

The pivot table fields are (I hope) fairly obvious:

I've just asked to show the two measures created.

Personally, I much prefer the previous approach!  I'd rather have multiple tables (each having a separate relationships) than have multiple relationships to the same single table.