Relationships and reporting (likely CROSSFILTER)

[don_writer]

Hi Gurus!

Here is my pickle:

I have an ActiveEmployee table:

UniqueID (data_as_of & employee_id)

data_as_of (last day of the month)

employee_id

company

division

department_id

headcount (always equals 1)

Separations table

uniqueID = data_as_of (last month) & employee_id

data_as_of (last day of the month)

termination_date (day of termination)

employee_id

department_id

termcount (always 1)

I have a Date Dimension table with the standard fields, Date, Month, Quarter, year, MonthNO, etc.

I have a transformed organization table where I redefine some Division and Company names. The Unique identifier on that table is department_id

I want to create a matrix, as an example. 

Rows - ActiveEmployee[company]

Columns - Month

Values = sum(ActiveEmployee[headcount]) AND sum(Separations[termcount)

Separations are not on that months ActiveEmployees (thus why the Separations UniqueID looks a month back).

This, of course, creates a situation where it won't display the most recent month's Separations due o the shift. I have the ability to adjust the employee_id, if need be.

How would you approach this? What would your model look like? And I am thinking the answer is likely in the relationships being one way and using a crossfilter for the sum of headcount and termcount. This feels like it should be simple and I have been doing this kind of data for ages but something seems to be escaping me.

Thanks for your help,

~Don

[v-lili6-msft]

hi, @don_writer

I also think the main problem should be in create the relationship process.

So could you please share some data sample and expected output . You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[LivioLanzo]

Hi Don, is it possible to post sample tables? and what you try to achieve?  thx