Forum Discussion
Date dimension and multiple date columns in fact table
You can have multiple relationships between your Fact table and your Date Dimension table.
So a FACT table with [Order Date] , [Ship Date] and [Delivery Date] can all be related to your Date dimension.
Only one relationship can be Active! You should set this to be the most common link.
Then you create measures per date action and use the DAX function called USERELATIONSHIP
https://msdn.microsoft.com/en-us/library/hh230952.aspx
So a measure counting the number of deliveries will specify that the Inactive relationship is to be used for that particular calculation.
A bit of a pain but it's not as bad as it sounds.
Otherwise use an MDX cube and you will have no such issues :)
Microsoft EmployeeYou can have multiple relationships between your Fact table and your Date Dimension table.
So a FACT table with [Order Date] , [Ship Date] and [Delivery Date] can all be related to your Date dimension.
Only one relationship can be Active! You should set this to be the most common link.
Then you create measures per date action and use the DAX function called USERELATIONSHIP
https://msdn.microsoft.com/en-us/library/hh230952.aspx
So a measure counting the number of deliveries will specify that the Inactive relationship is to be used for that particular calculation.
A bit of a pain but it's not as bad as it sounds.
Otherwise use an MDX cube and you will have no such issues :)
Being able to define multiple date relationships from the fact table to a date dimension is critical, and more times that not you have to use 2 or 3 dates at the same time so as I understand the proposed solution that will not work. How can PowerBI say it supports a Star model and not support the basics. Another example is the fact table has multiple healthcare provider keys joining to one provider dimension, again using multiple at the same time has to work. I am concerned the perceived work arounds suggested don't work and creating and managing many copies of a dimension also sounds terrible. I hope Microsoft has an enhancement plan. Thoughts?
- Phil,
Agree with you that USERELATIONSHIP can help solve the problems for different measures with different date for calculations.
What about the tables that needs to filter the data based on the different data.
To give you an example - I have summary report that calculates 5 different measures - Revenue, Expenses, Labor Hours, etc., each of this measure needs to be summarized with different dates. I can use USERELATESHIP on the measure calculation correctly, but displaying the detail data - Revenue line items based on the invoice date, expenses line items based on the transaction date, etc is a real challenge.. Not sure if there is elegant solution to deal with this scenario in Power BI,
a1b1c1Advocate I
Anna I'm experiencing the same issue where I have to display detailed data using two date columns where there's a date dimension with one active and one inactive relationships between them.
Still looking for an answer.I agree the work around isn't sufficient, and create a cube isn't the best solution. Especially when the future appears to be a fabric lake house/sql endpoint providing a direct lake connection. See reply below copied from above for more context, sorry for duplication.
Being able to define multiple date relationships from the fact table to a date dimension is critical, and more times that not you have to use 2 or 3 dates at the same time so as I understand the proposed solution that will not work. How can PowerBI say it supports a Star model and not support the basics. Another example is the fact table has multiple healthcare provider keys joining to one provider dimension, again using multiple at the same time has to work. I am concerned the perceived work arounds suggested don't work and creating and managing many copies of a dimension also sounds terrible. I hope Microsoft has an enhancement plan. Thoughts?
