Forum Discussion
Date Tables - Multiple Dates -
I think this is a fairly simple question. Before I go building a real mess of a structure, I thought perhaps I should make an inquiry.
I have a table that contains 5 different date fields. (Date Proposed, Date Shipped, Date Recieved, Date Reviewed, Date Consumed)
If I want to build various reports off each of the above mentioned dates, do I need a specific date table for each?
Then I create a Many-1 relationship for each field to its respective table. Is this the right approach, or a bloated approach?
Or, do I need to do research on ____________________ ?
Thanks,
You want just one date table. Create an active relationship between the key field on your date table and the most used date on your fact table. Then create inactive relationships for the other dates. When building measures for the date fields that have an inactive relationship you will need to utilize the USERELATIONSHIP function.
12_Monkeys You do not necessarily need a separate date table for each date field. Instead, you can use a single date table and create multiple relationships between this date table and your fact table. This approach is more efficient and avoids unnecessary complexity.
Establish relationships between the date table and each of the date fields in your fact table. However, Power BI only allows one active relationship between two tables at a time. Therefore, you will need to set one relationship as active and the others as inactive.
hen you need to use the inactive relationships in your reports, you can use DAX functions like USERELATIONSHIP to activate the appropriate relationship within your measures.
Create a single date table: This table should contain a continuous range of dates that cover all the dates in your fact table.
4 Replies
12_Monkeys You do not necessarily need a separate date table for each date field. Instead, you can use a single date table and create multiple relationships between this date table and your fact table. This approach is more efficient and avoids unnecessary complexity.
Establish relationships between the date table and each of the date fields in your fact table. However, Power BI only allows one active relationship between two tables at a time. Therefore, you will need to set one relationship as active and the others as inactive.
hen you need to use the inactive relationships in your reports, you can use DAX functions like USERELATIONSHIP to activate the appropriate relationship within your measures.
Create a single date table: This table should contain a continuous range of dates that cover all the dates in your fact table.
Thank you, very helpful information that has me reading up on this.
You want just one date table. Create an active relationship between the key field on your date table and the most used date on your fact table. Then create inactive relationships for the other dates. When building measures for the date fields that have an inactive relationship you will need to utilize the USERELATIONSHIP function.
Thanks for that, I will study up on this.
