Multiple Date Columns in FACT Tables

[ArchStanton]

Hi,

There is currently a new data model being built within my organisation and I'd like to check one thing with any experts on here.

My question is this, there are several FACT tables that contain multiple date columns which all tend to be used either in a measure or calculated column, and I understand only one can ever be used in a relationship with my Date Dimension table.

Would would be best practice in terms of the FACT tables where i have 6/7 different date columns?

I'm finding issues with measure being calculated incorrectly which are only remedied when I swap the date relationship.

Are the use of USERELATIONSHIP and CROSSFILTERS functions the answer to this problem or should the FACT Table be split in Power Query first so we have 1 or 2 dates max in each table?

Any thoughts or ideas would be much appreciated.

Thanks,

[johnt75 · Accepted solution]

I would say have multiple relationships and use USERELATIONSHIP to determine which one should be active for a given measure. Depending on your use case, a calculation group could be a good option with a calculation item per date. This would allow you to apply the same logic to all of your measures.

However, if you want to be able to show 2 different versions in the same e.g. line chart I don't think calculation groups would work, you would need to define the different measures each using their own USERELATIONSHIP

[ArchStanton]

Thank you, that is most helpful