Date Dimension Tables in large datamodels

[cwayne758]

Hi, 

I'm running quite a large data model (around 20 queries), with many of my tables containing multiple date columns.

I am curious how others approach the use of Date Dimension tables in large models. Do you leverage multiple DateDimension tables with  relationships to your different date fields, or use one Date Dimension table with multiple relationships?

Currently, I am trying to use one Date Dimension table but with my limited proficency in DAX, the filter contexing proves to complicat things for me.

Here's a photo of the model  

[Greg_Deckler · Accepted solution]

I posted something on Transitive Relationships and Date Dimensions that might help:

http://community.powerbi.com/t5/Desktop/Transitive-Relationships/m-p/8574#M1449

[lanceengland · Accepted solution]

You can pragmatically do either way.

Separate Data Tables
PROS

• Formulas easier to create for beginning DAX users

CONS

• Your data model will require more memory with duplicate tables
• If you want to add a new column to your date table (ISO week, for example), then you will have to add it to every table which is more work to maintain and uses more memory

If your data model size in memory is pretty small, you could use separate tables. I prefer only one Date table, creating multiple relationships to it, and altering the appropriate measures to add the USERELATIONSHIP function to the filter context parameter of the CALCULATE function. Once you get the hang of it, it's not as hard as it first seems.

[lanceengland · Accepted solution]

You can pragmatically do either way.

Separate Data Tables
PROS

• Formulas easier to create for beginning DAX users

CONS

• Your data model will require more memory with duplicate tables
• If you want to add a new column to your date table (ISO week, for example), then you will have to add it to every table which is more work to maintain and uses more memory

If your data model size in memory is pretty small, you could use separate tables. I prefer only one Date table, creating multiple relationships to it, and altering the appropriate measures to add the USERELATIONSHIP function to the filter context parameter of the CALCULATE function. Once you get the hang of it, it's not as hard as it first seems.

[leonardmurphy · Accepted solution]

One advantage of multiple date tables is that you can label the attributes clearly as to what they represent (e.g. Invoice Date, Invoice Month, Order Date, Order Month). 

It can also be helpful if you have 2 dates associated with the same fact and want to combine filters. (E.g. all returns in October 2015 that have an original purchase date of 2014).

[Greg_Deckler · Accepted solution]

I posted something on Transitive Relationships and Date Dimensions that might help:

http://community.powerbi.com/t5/Desktop/Transitive-Relationships/m-p/8574#M1449