Latest Enterprise DNA Initiatives

Effective/End Dates

Hi folks,
My source data has a position table, which assigns a position to each employee for an effective date and an end date.

When creating a data source for Power BI, should I expand the records (one row for each date between effective and end date)?

Thanks for any insights you can provide.

Hi @kaDargo,

As always the answer is: that dependsā€¦

Adding a row for each date can significantly impact model size BUT it will often make your DAX measures way more simpleā€¦ If you do decide to expand these Records to a daily level, make sure that table is as narrow as possible, in other words, only bring in columns you actually use in calculations and keys to connect to your dimensions, nothing else.

Furthermore I hope the goal here isnā€™t to build a ā€˜one-modelā€™ fits all. Thatā€™s always a bad idea, in my opinion. Having several smaller, well designed models are often way more performant and easier to maintain. You can always bring elements from different reports together in a single dashboard OR look into composite modelsā€¦

I hope this is helpful

Thanks for your response.

If I choose not to expand, do you have any recommendations for DAX calcs and relationships? How should I handle the BETWEEN?

Certainly. You can only ever have one active relationship between a Fact and a given Dimension table, this means possible other relationships between the two will always be inactive. Filters however will only flow through the active relationship to the fact table.

In some cases that is not desirable and two fields should be considered equal. You can either choose to have no relationships OR only inactive relationships between the Fact and that given Dimension table. When there is ā€˜no relationshipā€™ you can create one on the fly using TREATAS for example but in the case of an ā€˜inactive relationshipā€™ you can ā€˜activateā€™ it using USERELATIONSHIP inside your calculation.

A common example is ā€˜events in progressā€™ hereā€™s a link to a post by @Greg on that topic.

I hope this is helpful


Thanks for your time!