Forum Discussion
Not the usual Working Day Problem
Hi skasper,
As I said and based on your explanation the question is that you need to make you DimDate[date] as x-axis in your visuals to achieve the hierarchy and the correct values in your measures not the Effort Date since you have a relationship (that I assume is 1-Many) the visuals will be ok.
Regards,
MFelix
HI skasper,
Not abble to reproduce since the low information in your post, however if I understand correctly you want to calculate a number of measures based on the Effort Date, ion my previous experience with the this type of calculations and also having a DimDate table if I link the DimDate to the EffortDate your calculations mus be correct.
Sorry for asking but when you set-up the relationship are you making it in the correct date field in the work log entries?
One other thing that I notice is that you are making a slicer on the Effort_Date but then making the measure on your DimDate if you don't set-up your filter options in the relationship to both you are not abble to slice it back.
If you can share additional details I can help you better, but I believe it can be a small setup in your relationship.
Regards,
MFelix
Hi MFelix and thank you for the swift reply.
Let me try to clarify:
I tried to link the tables based on the respective Date fields ('Effort Date' in the work log and 'Date' in the DimDate table). However, once I do this, 'Effort Date' cannot be used as a hierarchical date field in visualizations anymore. See screen shots below.
Without Relationship between WorkLog and DimDates
No Relationship (Effort Date and Date)
Effort Dates as Hierarchy on X-AxisEffort Date Hierarchy
With Relationship between WorkLog and DimDates
Relationship between Effort Date an DateEffort Date not Hierarchical anymore
Individual Dates instead of Hierarchy
Regarding the measure: yes, this would only work, if there were a relation. An alternative would be to sum/count the 'weekday' in the actual work log table but there I face two more issues:
1. It is possible that there are no worklog entries on an actual work day, so the respective date would not even show up in the work log and would then be missing from the calculated number of work days.
2. I have multiple entries for any given day (appr. 200 employees). Hence, just counting the entries where 'weekday = 1' would give the wrong result.
Due to this, I think I need to count the working days in a separate ('neutral') table like DimDates, where I have exactly one entry for each date.
And this is the Catch22: If I do this and have NO relationship between WorkLog and DimDates, I cannot apply a date range filter across both tables.
I hope this makes it a bit clearer ...
Hi skasper,
As I said and based on your explanation the question is that you need to make you DimDate[date] as x-axis in your visuals to achieve the hierarchy and the correct values in your measures not the Effort Date since you have a relationship (that I assume is 1-Many) the visuals will be ok.
Regards,
MFelix
Frankly, I am not sure why I never thought of tackling it from that direction. I was just completely stuck on the Effort Date from the WorkLog.
Suffice to say, this works perfectly and you are my personal Hero of the Day.
Thank you for the fast and competent advice.
Sascha