BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
In the real world (or at least, the DAX version of it), calendars and dates aren't quite as simple as one would like. This blog shows how to solve a number of common scenarios, including where different tables have different levels of "granularity", coping with special days like bank holidays, handling non-standard finanical year-end dates and dealing with a table which contains more than one date column.
- Modelling techniques when working with calendars/dates
- Dealing with varying date granularity using calendars
- Modelling bank holidays and other special days
- Modelling different financial year-ends
- Handling multiple date columns using multiple tables
- Handling multiple date fields using multiple relationships (this blog)
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.
Posted by Andy Brown on 22 January 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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.
Please first follow the steps in the previous part of this blog to recreate the model containing the two tables shown above (including expanding the calendar table to go back to 1st January 1950).
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:
Number staff joined:=COUNTROWS(
CALCULATETABLE(
Staff,
USERELATIONSHIP(Staff[DateJoined], Calendar[Date])
)
)
And here's the measure to count the number of staff born:
Number of staff born:=COUNTROWS(
CALCULATETABLE(
Staff,
USERELATIONSHIP(Staff[DateBorn], Calendar[Date])
)
)
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.
- Modelling techniques when working with calendars/dates
- Dealing with varying date granularity using calendars
- Modelling bank holidays and other special days
- Modelling different financial year-ends
- Handling multiple date columns using multiple tables
- Handling multiple date fields using multiple relationships (this blog)