Forum Discussion
Training Programs & Enrollment Data
I am looking for support in relating two datasets to understand the following about the people in our firm: (1) for each of our available training programs, how many people have attended (2) how many people have NOT attended each of those programs.
I currently have two datasets which are related by a User ID column. The first data set has unique User IDs and contains a list of all users for the firm along with other people-related data (years of service, location, job-level). The second data set is an export from our learning software which includes a line item for each training program an individual has taken, such that users will show multiple times for different programs, but those who haven't taken ANY programs will not show up in the learning software report.
Dataset (1) - People Data
| User ID | Years of Service | Location | Job Level |
| 1001 | 20 | Mexico | 6 |
| 1002 | 6 | Canada | 6 |
| 2003 | 7 | Spain | 7 |
Dataset (2) - Learning Report
| User ID | Program | Year Attended |
| 1001 | Talent Prog A | 2012 |
| 1001 | Talent Prog B | 2019 |
| 1002 | Talent Prog C | 2022 |
I would like to be able to relate these data sets in a way to generate insights such as:
- How many people have attended Talent Prog A, how many HAVE NOT attended Talent Prog A
- For Job Level 5 - how many have attended Talent Prog C
- For Talent Prog B - what regions have sent the most people?
Thank you for the help!
4 Replies
WetGruul , you need one more table, a list of all the training programmes that a user could attend (this will be a dimension table):
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html
The list of attendance records is the 'fact' table.
Then you need to select the 'show items with no data' option in the visual you use so that when you create a table of user from the dimUser and programme from the dimProgramme table, it will show all the blanks.
AllisonKennedy - thank you for the support! - I've connected the datasets as you've described, checked "show items with no data" for the program title and run into the following issue.
When I use the Count (distinct) UserID from the training report table (the fact table) I get a count of all of users who have attended each different training program. When I use the User ID from the people data table it simply gives a count of everyone in the people data table. I was expecting this to filter, is that not the case?
Additionally, while the count of user is great to understand who has attended, i'm truly interested in those who have not attended and would like to be able to filter to just those user IDs and gain insights into the demographics (job level, etc. which is in the people data table) of individuals who have not taken specific training. Thanks!WetGruul As Ashish_Mathur has already hinted at, you need to create a measure to filter the dimUser table. Due to the 'cross filter direction' being single many to one relationship (which is the correct way to set up the model), the dimUsers table doesn't get filtered, as you have discovered. Thus you just get the total number of users for each course.
There are a few ways to get the number of users who didn't attend, depends how you want it to interact with other slicers and filters. You could use Ashish's method, or just a simple subtraction.
[Total Users] = COUNTROWS(dimUser)
[Attendances] = COUNTROWS(factAttendance)
[Attended] = COUNTROWS( FILTER( dimUser, [Attendances] > 0 ) )
[Not Attended] = COUNTROWS( FILTER ( dimUser, ISEMPTY( factAttendance ) )
All as new measures
Hi,
Create a Program slicer and select a talent entry there. For question1 (those who did not attend), try these measures
Attended = distinctcount('Table2'[User ID])
Not attended = countrows(filter(values('Table1'[User ID]),[attended]=blank()))
Hope this helps.
