Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Straight to the point: how does one deal with multiple columns containing date values in the master fact table, and a separate date dimension?
The way our data warehouse tables are structured, a single fact table can contain anywhere between 2 and 20 date values. To give an example, a product table can have separate columns with date values for product creation, update, deletion, replacement, last use, etc. Some are populated consistently, others can have null values.
I'd like to add a date dimension to my model, but in Power BI I can only relate the date column in DimDate to a single date column in the fact table.
I don't have to use a date dimension, strictly speaking, since I can just use Power BI's built-in time intelligence features and create multiple "mini" date dimensions for each date column right inside the fact table, but it doesn't seem like a very legant approach...
Solved! Go to Solution.
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 🙂
I am having the similar issue. Do we have an elegant solution in such case.
You want to user CROSSFILTER next to USERELATIONSHIP. It will ignore date filtering that comes from an active relationship and use the one that is set with USERELATIONSHIP. So 5 measures with different dates in one table, will use different dates to calculate, when single filter is being used.
CROSSFILTER and USERELATIONSHIP is not needed for the measures that use active relationship.
CALCULATE (
CALCULATE (
SUM ( 'Table'[Column 1] ),
USERELATIONSHIP ( 'Table'[Date 1], 'Master Calendar'[Date] )
),
CROSSFILTER ( 'Table'[Date 2], 'Master Calendar'[Date], NONE )
)
@Anonymous Thanks for getting back to me instantly.
It's the case that I want to display two date columns ('Attended On' and 'Registered On') in a simple table visual. I don't want to create any calculations like sum of amount etc based on those dates. As you can see in the screenshot attached, there are some blank values in both the columns.
I have an active relationship of 'Attended on' with Date Dimension and an inactive relationship with 'Registered On'.
I was using this measure to activate the 'Registered On' relationship:
It poses two problems, one of which you had mentioned in your previous reply. It ignores the active relationship filtering. Second, it doesn't display values where the date value is blank. Somehow, the dax relationship only returns values where the resulting date value is not blank.
I tried using your measure, it wasn't working somehow. Can you please share any ideas as to how to get this working?
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 🙂
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?
@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?
^^ This needs more attention
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |