562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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).
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 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.
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:
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.
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.