Forum Discussion
Training Programs & Enrollment Data
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