Table Relationship Query

[jamiefisher]

I have a draft visual to view a staff list, identify what training they are required to have based on their positon and display where that related training has taken place or not.

I can get two seperate visuals to work but cannot consolidate it, I am sure I have an issue with links but would appreciate comments or suggestions on best to achieve this.

As soon as I add in any fields from training table the position training requirements are removed as below

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[Alef_Ricardo_]

It seems like you're trying to create a visual that displays a staff list with required training based on their position and also indicates whether that training has taken place. It's essential to ensure that your data model and relationships are correctly set up to achieve this. Based on your description, you can use a combination of tables, slicers, and matrix visuals. Here are the steps to achieve this:

1. **Data Model Setup**:
Ensure that you have the following tables in your data model:
- Staff table with information about employees and their positions.
- Training table with information about training courses and related fields.
- A bridge table that connects employees to training courses, indicating which employees have taken which courses.

Ensure that the relationships between these tables are correctly established. The relationship between the Staff table and the Training table should be through the bridge table.

2. **Create a Matrix Visual**:
Create a matrix visual with the following fields:
- Rows: Employee Name from the Staff table.
- Columns: Training Name from the Training table.
- Values: Count of Training (use the bridge table) to show how many times a training course has been taken by each employee.

3. **Filter Training Requirements**:
To display required training based on positions, add a slicer visual to your report. This slicer should allow you to select positions, which will filter the matrix visual. This way, you can see which training courses are required for employees in the selected positions.

4. **Highlight Missing Training**:
You can use conditional formatting to highlight missing training. For example, you can format cells where the Count of Training is zero with a specific background color to indicate missing training.

5. **Use Drill-Through or Tooltip**:
You can set up a drill-through feature or tooltips in the matrix visual to view more details about the training courses or the specific employees' training records.

This approach allows you to view staff lists, identify required training based on positions, and see whether the training has taken place. If adding fields from the Training table is causing issues with your visualization, ensure that the relationships between your tables are set correctly, and the filtering is configured appropriately. Additionally, make sure that the Training table fields do not interfere with the employee position requirements.

[Data-estDog]

What fields are your relationships based on? They should alway use Primary and Foreign key. Just looking: Staff to Training table should be based on Emp No. Using a 1 to * relationship. I am guessing you used position.

I agree with @ray_aramburo who said to delete the other relationship between TNR and staff... which is likely on based on position field. 

[ray_aramburo]

First, delete the relationship between Staff and TNR. 

Second, the relationship between Staff and Training should be One to Many. Not Many to Many. 

Fix that and see your outcome, if still you're not seeing what you are expecting, go to the options arrow in the Course Code field and select "Show Items With No Data".