Forum Discussion
Loose my column dates when adding values or rows in matrix
Hello. Can anyone tell me why when I add in my rows field I loose most of my weeks in my columns?
I’m trying to show a matrix of sum oof billable hours (values) across weeks(columns) by project (rows)
I have my week nums in the column
But when I add in my values
Or worklog ID
I only get the last 2 weeks. I guarantee there I data for these worklogs for the whole year – I can’t figure out why all the weeks aren’t showing up – there are no filters applied anywhere
I have my WORKLOG_DATE mapped to the Date in my Calendar date table as a many to One
I'm sure it;s something stupid I'm just not seeing. Thanks in advance
This is the solution to this topic posted by TheoC
Okay, so I figured out what the cause is. The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field. You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only
Hi ctedesco3307
Glad I could help with the "Okay, so I figured out what the cause is. The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field. You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only "
In all honesty, I don't know the exact reason that the Date and Date/Time didn't work initially. It could have been for a variety of reasons but within this example, I am unsure.
All the best with your Power BI journey my friend.
Theo 🙂
11 Replies
This is the solution to this topic posted by TheoC
Okay, so I figured out what the cause is. The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field. You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only
Hi ctedesco3307
Glad I could help with the "Okay, so I figured out what the cause is. The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field. You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only "
In all honesty, I don't know the exact reason that the Date and Date/Time didn't work initially. It could have been for a variety of reasons but within this example, I am unsure.
All the best with your Power BI journey my friend.
Theo 🙂
Hi ctedesco3307
A couple of things just to double check:
- The relationship from the Date to Fact table is one to many (one from Date, many to Fact table).
- Ensure that your Date table has all of the dates required in it.
- On your visual, make sure the Week Numbers is from your Date table.
Hope this helps 🙂
Hi TheoC - Thank you for the quick response, but all the things you mentioned are in tact.
- The relationship from the Date to Fact table is one to many (one from Date, many to Fact table). - it is -- please see image in post
- Ensure that your Date table has all of the dates required in it. - it does. It ha every day in the calendar year
- On your visual, make sure the Week Numbers is from your Date table. - It is -
Thank you for trying to help!
TheoC - Sure - you need alot of rows though to se all the worklogs against all the calendar dates - and I can't attach files to these posts. is there a better way to get you what you need?
Let me know if this works - you have to request a share when you click on it
ctedesco3307 just gained access. Will touch base shortly.
Hi ctedesco3307
Okay, so I figured out what the cause is. The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field. You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only (see below screenshot).
I am sending you a link to PBIX via private message 🙂
Hope this helps 🙂
Theo
ctedesco3307 are you able to mark the above post as the solution?
Hi TheoC - Ok - I took your suggestion one step further and created a date columns using Datevalue(worklog_worklog_date) and that worked! So I ammend my above post to confirm you provided the correct solution, although i don't understand why both TDate and Worklog_Worklog_Date being date/time or both being Date don't work. That doesn;t make sense to me. If you can explain that - would be super helpful - if not - ok. THANK YOU for the solution.