Setting up relationships between 3 tables - CrossFilter?

[Mal_Sondh]

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

[Ashish_Mathur]

Hi,

Show the expected result.

[Mal_Sondh]

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 · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Mal_Sondh]

thanks @Ashish_Mathur 

[Ashish_Mathur]

You are welcome.