This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Ways to model different DAX calendar date situations Part six of a six-part series of blogs
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.

Ways to model different DAX calendar date situations

Part six of a six-part series of blogs

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

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:

=USERELATIONSHIP ( First column, Second column)

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.

Parts of this blog
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)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR