I have a requirement where i need to bypass a dimension filter on a date field. Let me elaborate a mock up use case-
I am working on roster data for 3 employees - say Alice, Tom and Bob who were hired on a 2 weeks contract in ABC firm.
The user layout as below -
The issue is that if the staff is on leave or if its weekend (Sat/Sun), i still want the date to appear with start time, end time and other fields as blank.
I tried data blending to calendar table and also left join with calendar table but it didn't work. The problem is that as soon as a i put a filter on staff name and upcoming weekend date which user wants to select, saturday, sunday or days when staff is out of office disappears.
When i used left join approach, i used FIXED LOD on calendar date form minimum calendar date, it didn't work (Worksheet 2 in attached workbook). I also tried to exclude staff dimension using EXCLUDE LOD, but no success in that too.
I also tried data blending approach from calendar to transaction table but i am not able to get desired layout(i am running out of aggregations for LOD when i apply it on date field).
I can transform or cross-join to have my calendar data repeated at employee level(separate calendar for each employee) and than do composite blend. It needs an ETL solution and i don't want to go that way. I think there would be a solution in tableau without need of data densification.
I have attached dummy workbook, if someone has a solution to this problem please help !!!