Hi,
I have a dimension table for time of day (TOD) with 3 options: "1" = day shift, "2" = evening shift, and "3" = night shift.
I frequently chart number of shift per time of day (TOD) based on the "shift codes" logged in our scheduling system. Some shifts (and hence shift codes) span more than one TOD. e.g., "A" shift is 8 hours on day shift and 4 hours on evening shift. The way our scheduling system works is that "A" gets recorded twice:
Entry 1: "A 8 1"
Entry 2: "A 4 2"
The above translates to:
Entry 1: "shift code A, 8 hours on TOD 1"
Entry 1: "shift code A, 4 hours on TOD 2"
An issue I'm facing is that if I simply COUNT instances of 'A' shift I get different answers based on what TOD I'm filtering for. If I'm looking at just TOD =1, then count =1 (desired and correct result). If my slicer is showing TOD 1&2, then count of 'A' is 2 (a misleading result but technically correct math given the data structure). I desire to set my shift count based on what TOD is selected in the slicer, such that if only one TOD is selected (1 OR 2 OR 3) then count as normal, but if TOD selected is (1 AND 2; 2 AND 3; or 1 AND 2 AND 3) then count for 'A' shift should be divided by 2.
I have in the past created a measure that changes value based on a slicer (SELECTEDVALUE and SWITCH functions using a disconnected table). However, I'm unclear how to do this with multiple selected values.
Is this possible? Thanks!
