Splicer defining one Table but not the other...?

[BradWellings]

Hi all,

I am attempting to create a Report which has two tables on the same Page.

1. New Employees per reporting period (Name, Job Title, Date Hired etc)
2. Exited Employees per reporting period (Name, Job Title, Date Exited etc)

The reporting period is defined using a Splicer which is connected to a Date Table. This Date Table is related to the Hire Date and is giving the correct info for Table (1). However, Table (2) shows only those that are Hired and Exited within the same reporting period. Presumably as the active relationship is between the Date Table and the Hire Date. Therefore those that were employed in previous months/years are not captured by the inactive relationship between the Date Table and End Date.

Any idea how I can get the second table to give me all employees that exited during the same period? I realise I could put them on separate Pages with separate Splicers however I'd prefer to avoid that.

Thanks

Brad

[v-huizhn-msft]

Hi @BradWellings,

It's hard to provide a detailed solution which is close to your requirement without sample table. Do you mind share your sample table or .pbix file for further analysis?

Best Regards,
Angelia

[JurreVanGool · Accepted solution]

Hi Brad,

Can you post a picture of how the datamodel works? I'm guessing the hire and exit data resides in one facttable, right? Then your presumption "Presumably as the active relationship is between the Date Table and the Hire Date" is right.

Easiest (but probably not the best) solution is to split the fact table into 2; Hires (includes hire date) and Exits (includes exit date)? This allows for two seperate active relation and the use of one splicer. 

Hope this helps,

Regards,

Jurre

[BradWellings]

@JurreVanGool- You're right. It was a single fact table and after racking my brain and searching for solutions, I ended up splitting the table. Thanks.