Forum Discussion

Mal_Sondh's avatar
Mal_Sondh
Icon for Helper II rankHelper II
5 years ago
Solved

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 KeyMonth EndVersionSnapshot Date
530-Apr-21106-May-21
431-Mar-21208-Apr-21
331-Mar-21101-Apr-21
228-Feb-21105-Mar-21
131-Jan-21105-Feb-21

 

Exceptions Table:

 

Exception IDException TypeException EmployeeSnapshot Key
1Missing DataEmp1114
2Missing DataEmp2224
3Missing DataEmp3334
4Duplicate NameEmp4443
5Duplicate NameEmp5553

 

Employee Table:

Employee NumberDivisionDepartmentSnapshot Key
Emp111AAABBB1
Emp222BBBCCC1
Emp333CCCDDD1
Emp444AAACCC1
Emp555BBBCCC1
Emp111AAABBB2
Emp222BBBCCC2
Emp333CCCDDD2
Emp444AAACCC2
Emp555BBBCCC2
Emp111AAABBB3
Emp222BBBCCC3
Emp333CCCDDD3
Emp444AAACCC3
Emp555BBBCCC3
Emp111AAABBB4
Emp222BBBCCC4
Emp333CCCDDD4
Emp444AAACCC4
Emp555BBBCCC4
Emp111AAABBB5
Emp222BBBCCC5
Emp333CCCDDD5
Emp444AAACCC5
Emp555BBBCCC5

5 Replies

    • Mal_Sondh's avatar
      Mal_Sondh
      Icon for Helper II rankHelper 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