Forum Discussion
Helper IDate table relationship with multiple dates in one table
I have created a date table and thought I could link it to multiple date fields in another table but it appears I can't. The CBR-Report table has multiple FC date columns (forecast) and I'm wanting to create a date slicer that would apply to all those date fields at the same time.
I'm wondering if I have to split out the CBR report into multiple tables for SAQ,, SAT etc which have their own FC date columns?
Any help much appreciated!
cheers
Ness
Hi NessFlood , Thank you for reaching out to the Microsoft Community Forum.
Power BI can’t directly apply one slicer to multiple different date columns in the same table through relationships alone. The workaround is to unpivot those date columns into a single Date column (with another column for Date Type like SAQ_FC, SAT_FC, etc.) in Power Query. This way, you can create one relationship from the unpivoted Date column to your Date table and your slicer will naturally filter all forecast dates at once, since they now live in a single column instead of being split apart.
If restructuring the model isn’t an option, you can keep the relationships inactive for each date field and build separate measures using USERELATIONSHIP, then combine those measures in visuals. While this works, it won’t give you a truly unified slicer experience,unpivoting remains the only tidy, model-friendly way to have one slicer drive all those dates together.
4 Replies
- danextian
Super User
Hi NessFlood
You can create only one active relationship between two tables. The additional relationships will be deemed inactive which can be invoked by using USERELATIONSHIP function in a measure. In the screenshot below, the ones with broken lines are inactive. Please read this: Active vs inactive relationship guidance
Please see the attached pbix for the details.
NessFloodHi there
How does this help me create a date slicer?
I want to be able to apply a date filter to my report that returns all the FC dates (forecasted dates) for multiple tasks.
cheers
Vanessa
v-hashadapuCommunity Support
Hi NessFlood , Thank you for reaching out to the Microsoft Community Forum.
Power BI can’t directly apply one slicer to multiple different date columns in the same table through relationships alone. The workaround is to unpivot those date columns into a single Date column (with another column for Date Type like SAQ_FC, SAT_FC, etc.) in Power Query. This way, you can create one relationship from the unpivoted Date column to your Date table and your slicer will naturally filter all forecast dates at once, since they now live in a single column instead of being split apart.
If restructuring the model isn’t an option, you can keep the relationships inactive for each date field and build separate measures using USERELATIONSHIP, then combine those measures in visuals. While this works, it won’t give you a truly unified slicer experience,unpivoting remains the only tidy, model-friendly way to have one slicer drive all those dates together.
- v-hashadapu
