Forum Discussion
Setting up relationships between 3 tables - CrossFilter?
Hi All,
I have the attached 3 tables and modeled as shown.
Snapshot table (1)
Exceptions Table (2)
Employee Table (3)
I need to show the emloyee information for where they have an exception (Exception Table), how do i create a link between the Exceptions table and the Employee Table - this would be a many to many relationship.
I would like to have a visual table to show all the employee information for each Exception type and snapshot key.
Any ideas? Can I use CrossFilter and if so, how would that work?
I had tried this but it didnt give me the ability to view the employee table attributes..
Measure = CALCULATE(COUNT(Exceptions [Exception Type], CROSSFILTER(Exceptions [Snapshot Key],Snapshot [Snapshot Key],Both),
CROSSFILTER(Employee [Snapshot Key],Snapshot [Snapshot Key],Both))
Snapshot Table:
| Snapshot Key | Month End | Version | Snapshot Date |
| 5 | 30-Apr-21 | 1 | 06-May-21 |
| 4 | 31-Mar-21 | 2 | 08-Apr-21 |
| 3 | 31-Mar-21 | 1 | 01-Apr-21 |
| 2 | 28-Feb-21 | 1 | 05-Mar-21 |
| 1 | 31-Jan-21 | 1 | 05-Feb-21 |
Exceptions Table:
| Exception ID | Exception Type | Exception Employee | Snapshot Key |
| 1 | Missing Data | Emp111 | 4 |
| 2 | Missing Data | Emp222 | 4 |
| 3 | Missing Data | Emp333 | 4 |
| 4 | Duplicate Name | Emp444 | 3 |
| 5 | Duplicate Name | Emp555 | 3 |
Employee Table:
| Employee Number | Division | Department | Snapshot Key |
| Emp111 | AAA | BBB | 1 |
| Emp222 | BBB | CCC | 1 |
| Emp333 | CCC | DDD | 1 |
| Emp444 | AAA | CCC | 1 |
| Emp555 | BBB | CCC | 1 |
| Emp111 | AAA | BBB | 2 |
| Emp222 | BBB | CCC | 2 |
| Emp333 | CCC | DDD | 2 |
| Emp444 | AAA | CCC | 2 |
| Emp555 | BBB | CCC | 2 |
| Emp111 | AAA | BBB | 3 |
| Emp222 | BBB | CCC | 3 |
| Emp333 | CCC | DDD | 3 |
| Emp444 | AAA | CCC | 3 |
| Emp555 | BBB | CCC | 3 |
| Emp111 | AAA | BBB | 4 |
| Emp222 | BBB | CCC | 4 |
| Emp333 | CCC | DDD | 4 |
| Emp444 | AAA | CCC | 4 |
| Emp555 | BBB | CCC | 4 |
| Emp111 | AAA | BBB | 5 |
| Emp222 | BBB | CCC | 5 |
| Emp333 | CCC | DDD | 5 |
| Emp444 | AAA | CCC | 5 |
| Emp555 | BBB | CCC | 5 |
5 Replies
- Ashish_Mathur
Super User
Hi,
Show the expected result.
- Mal_Sondh
Helper II
Example as follows - the snapshot Key (will be represented by a date picker as a filter) - but for the exceptions noted, i want to see all employee details - in this example i have reduced the size of the employee table but as is a lot wider in reality.
The join between the Exceptons and Employee table is the issue..
Any help would be appreciated.
Thanks
- Ashish_Mathur
Super User