Hi Everyone,
Hitting my head against a wall on this DAX for a while now, and figured it was well past time to see if anyone has insights on what I'm trying to do. My goal is to create a raw data dive that reflects data as of a certain date for teams to sort through. The issue I'm having so far is that when I try to do that, if there is a change in value to the employee's data, it will duplicate lines of data where the only difference will be to reflect that change, rather than show the appropriate one.
Data Structure
The data table I'm working on is effective dated. The columns I care about are:
'Employee ID', 'Employee Key', 'Effective Date', 'End Effective Date'
Employee ID is the ID number associated with Each Employee. Each Employee ID will have multiple records attached to it, each of which will have a beginning date and ending date (Effective Date and End Effective Date respectively)
Employee Key is an index column where each row gets a distinct number to help identify it in the full table.
Headcount Measure
Dax is Below, but the goal of this measure is to show which records per employee ID are active within a time period (there are additional filters involving employee status that aren't relevent here but are built into the Headcount Measure Dax). This Dax works and I don't have issues with it but it's relevant to the question below.
HEADCOUNT_EOP =
VAR _date = MAX('Date'[Date])
RETURN CALCULATE(
DISTINCTCOUNT('Timekeeper History'[Employee_ID]),
_date >= 'Timekeeper History'[Map.PSJob.EffectiveDate] && _date < ('Timekeeper History'[Map.PSJob.EndEffectiveDate]),
'Timekeeper History'[RecordEndDate] >= 'Timekeeper History'[Map.PSJob.LastHireDate]
)
Here's what I'm Trying To Do
My headcount measure above flags records that are "live" when I move around in my timeline. So when I change a date filter, or map out a bar graph to reflect timeline, it will count the appropriate records accordingly.
Is there a way to create a dynamic employee key that basically goes:
IF HEADCOUNT_EOP = 1, [HERE IS THE EMPLOYEE KEY THAT IS ASSOCIATED WITH THAT LINE OF DATA], "" ?
My goal here is then to be able to filter out rows of data where the dynamic employee key =/= that line of data's employee key. Everything I have tried has gotten mad at me with how many variable maximum values I've been playing with.
Is this doable? Does this make sense?
