Table Connections

[MoYassD]

I have tables with employee names.

- Table 1 is a list of cases managed by different employees so each employee has more than one row in the table as they have managed over 20 cases each

- Table 2 is a list of queries each employee has handled, again they appear multiple times they have handle a lot of queries

I ideally want to chart from both tables on one tab and use one slicer so all the charts change when I select a name.

How can I do this?

[PearlSchneider]

Thanks, I will keep it in my mind.

[CoreyP]

What you need is an employee dimension table. The employee dimension table will have a distinct list of all possible employees in your model. You can create this dimension table in Power Query from your existing tables. 

In your Table 1, right click the column header of your employee name field, and select Add as New Query. This creates a new query with all the values of that column in a list. You need to then convert to table:

Repeat these steps for Table 2. You now have 2 additional queries, each with a single column for employee name containing all the values from their source tables. With either of these new queries selected, go to Append Queries > Append as New. This results in a new query with a single column of all the values in Employee Name that occur in both Table 1 and Table 2. This now needs to be a distinct list, so right click the column header and remove duplicates. 

Name this new table dim Employees or something. Then you can create a 1:* relationship from this dim table to Table 1 and Table 2. Now you have a proper star schema data model.

[CoreyP]

@VijayP @Anonymous ,

I don't think merging is the proper solution due to cardinality. I also don't think this is a valid use-case for a many to many relationship. Many to many relationships should be avoided whenever possible, and proper star schemas should be created always.

[Anonymous]

Hi @MoYassD 

Based on your needs, I've created two tables

You can then set the relationship between the employee name fields in the Model View for the two tables

Create a slicer and drag the employe name into the slicer

Then you'll be able to achieve the effect you want

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[VijayP]

@MoYassD  Merge Both tables with required columns which gives you a single Table from there you can achieve the required result