Filters & Drill Throughs - Is there another way?

Crisocir

I find myself in this pickle because of the nature of my data. I have employee data that is batched monthly. So each month has all people ever employed at the company(along with info such as hire date, termination date, position, department etc..). I've already done some manipulation in ETL so that when an employee is terminated, the month of their termination is their last entry. 

Because I can have 1, 2, or 3 entries per employee per quarter I couldn't find a way to graph by quarter without my counts being over actual value. So in ETL I created a column to work as a flag for the last entry for each employee for each quarter. Filtering by this flag gives me the exact quarter graph that I want, however I wanted to drill through to show monthly data of that quarter. Because I am filtering for that flag column to get accurate quarterly data I cannot drill down to monthly data because the data is filtered out in the main card. 

 

I cannot thing of a reasonable work around that would allow me to see both accurate quarterly and monthly data within the same drill path. 

 

I'm open to any and all suggestions, below are charts of monthly data and then how the filter is used to get the quarterly view. 

 

GrantSmith

Hi @Crisocir 

 

If you're populating a second column with the last date is this column NULL for records where it's not the last date? If that's the case can you graph a count of this second column and not a count of the original column?

jaeW_at_Onyx

ok, so if i understand you correctly,

every month you take a snapshot of the employee table.  and then you also have a flag that says 'most recent record' or something like this that gives the current state of the employee.  and you want to navigate from the 'most recent record' to a previous month.

 

what you could do is have two datasets (use a dataset view, beta feature, talk to your CSM).  

 

create a dataset view that just has 'most recent records' apply the filter in the view so you don't have to apply the filter in the card.

 

then as you drill down navigate to a different dataset that has all your data.