Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
So this may be a beginner type question but I am having issues with this.
I am creating an aggregation import table that I want to be by month. However, my calendar table is actually by date time for the fact table.
How can I connect it so users can use the date dimension table for the calendar yet it fires off the aggregation.
I tried putting a Month Date in the dimension and then connect that to my aggregation it yells and says the aggregation can't have a bi-direction filter on it.
Solved! Go to Solution.
It is bi-directional becuase there are 'many' rows in the date dimension for a single month. You would need to do 1 of 2 things (there might even be more than 2 🙂
1. Create a view of Date Dimension to only have one row per month and join on view
2. In the aggregation table, add a column for the first day of month (or last day of month) and join on date dimension by this new column (1/1/2021 or 1/31/2021)
It is bi-directional becuase there are 'many' rows in the date dimension for a single month. You would need to do 1 of 2 things (there might even be more than 2 🙂
1. Create a view of Date Dimension to only have one row per month and join on view
2. In the aggregation table, add a column for the first day of month (or last day of month) and join on date dimension by this new column (1/1/2021 or 1/31/2021)
Hi @3CloudThomas , I was looking at this again and have a follow up question.
If I went with option 1 do you have any recommendations on how I could reconcile what would end up being 2 date dimensions?
1 would be the calendar date dimension that has every day in it. The 2nd would be the monthly one you suggested above.
Thanks
I did exactly what your #2 suggestion is. Seems to work like a charm.
Thanks!
Awesome!!!
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |