Needing Basic Data Modeling Support

[amypeters]

Hello, 

Ideally, I'd love to get on a call with a support expert to review my current report project but am unsure if that service is available to all users. Please advise. 

In essence, I am looking to build a dashboard where I connect two, unrelated tables via one LookUp table that carries a unique identifier. 

• Table A is a list of trainings completed by employees with Employee ID as the repeated value. 
• Table B has a list of teams which are grouped by employees and Employee ID can be a repeated value if an employee represents more than 1 team.
• Table C is the LookUp table that has just a single list of employees with Employee ID and no repeated values. 

I want to connect Table A & B using Table C so I can group by teams, but seem to be getting stuck. 

Let me know if you think you can help! Thank you!!

[Anonymous]

Hi @amypeters ,

Thanks for Idrissshatila's reply!

And @amypeters , We may not be able to accurately recreate your data model with textual descriptions alone, and then we won't be able to provide the right solution.
Could you please provide us with your sample data? You can use the sample data to replace the original data in your Table A, Table B and Table C, and then provide us with the three tables and their relationships, preferably with your expected results, so that we can more easily provide an accurate solution, thank you!

Best Regards,
Dino Tao

[amypeters]

Hello! Thank you for your response and intentions to help. I created three example spreadsheets of what I am hoping to accomplish and will do my best to lay it out here.

Table A - List of trainings completed by employee(s). This is a fact table due to no unique value being available. 

Table B - List of Teams and their team members. This also has employee ID to use as a connector as well. You'll see Jason Owen is a part of two teams, which is a real life example that is creating the most difficulty in my project. 

Table C - is a look up table that connects the employee name to their employee ID, which can connect the other two fact tables. 

As mentioned above, I want to figure out a way to connect Table A to Table B, meaning I want to know which teams and their corresponding team members had which trainings. I thought Table C seemed like the best choice, but I realized a couple issues. 

1. In order to pull the Team Name from Table A and # Training Events from Table B, I have to set the cross filter direction to "both" which I am nervous to do as it may cause some innaccuracies. Without that cross-filter direction, I cannot speak to the two fact tables. 
2. If the cross-filter direction is set to both, I noticed an error in the report. John Smith's Team is said to have two training events, even though he only had 1. That is because his assistant, Jason Owen, attended a separate training event with his other team, Jared White, which caused Jason Owen's name to be counted twice. See below. 

I'm not sure how to attach my example tables, but am happy to send if there is a way. Thank you so much!!

[Idrissshatila]

Hello @amypeters ,

I suggest you understand the Star schema data modeling, check these:
https://youtu.be/7ciFtfi-kQs?si=Z7eq_BhAXgzmudz3

https://youtu.be/1CgG0t59WcM?si=arAWj9_stlYeQ7Lg

feel free to reach out to me on linkedin if you need any help 
https://www.linkedin.com/in/idriss-shatila-powerbideveloper/

subscribe to My Youtube Channel
https://www.youtube.com/@PowerBISolution

[amypeters]

Thank you for your response! I did watch your videos and believe I am using the model correctly, but am still having trouble appyling to my situation. I provided an example, above, to another response and am hoping to get some guidance as to where I am missing the mark. Thank you again!!