Data modelling advice - multiple factless fact tables and date tables

[jon_w]

Hi

I'm creating a new data model which so far looks like this, the tables in red are my (factless) fact tables

But I'm wondering what is the best way to handle date table(s), for example I will want to filter and report by a number of different dates such as:

Event Date (dim table)
Event Registration Date (fact table)
Email Activity Date (fact table)
Marketing Email Date (dim table)
Contact Acqusition Date (Currently dim table, although as I type thinking this might be better as a 3rd fact table?)

You'll notice I also have a disconnected date table which I was using to filter tables via DAX. But looking to get some pointers on best practice for a data model like this, I was hoping to build a robust model that will serve for the majority of our events and marketing report.

Thanks in advance!

[Anonymous · Accepted solution]

HI @jon_w.

What type records did these two fact table stored? Did they store with the same data structure or stored records with summary and detail level records?
If you mean the first one, I'd like to suggest merge two table with custom fields to remark the root table of these records. If you mean summary/detail level, you can build main relationship on the summary level table(star schema) and link detail table with summary table instead directly build all relationship keys on these two tables.(current power bi data model only allow one active major relationship, other will auto turned to inactive or they will cause the conflict about relationship and aggregation)

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

BTW, for some disconnected tables interactions, you can create filter/slicer to apply filter affect. these effect can be extracted and applied in dax expressions.

Regards,

Xiaoxin Sheng