Ok so what I did is create a sample excel file with 3 different tabs:
- Registrations
- New Students
- Graduated Students
The real data has these as 3 seperate files with the registration file having over 30,000 students and the number of courses they completed. The new student file has about 14,000 students and their IDs. The graduated file has about 4,000 students and their IDs. So the common field between all files will be the Students ID.
The first thing I need to do is see how many students in the 'New Students' have registrations in 'Registrations' then I need to display the [Completed Registrations] next to their [Student ID] from the 'New Students' tab/file. I assume this would be similar to a VLOOKUP in Excel, which I have never done or had a reason to do until now.
The second thing I need to do is see how many students in the 'Graduated Students' tab/file have registrations in the 'REgistrations' tab/file then I need to display the [Completed Registrations] next to their [Student ID] in 'Graduated Students' tab/file.
I wondered if the 'Registrations' tab/file could be my central source and I column just somehow create a calculated column in Power BI
- one that displays the Student ID from the 'Graduated Students' tab/file if the ID from 'Graduated Students' is equal to the ID from 'Registrations'. If no match then display No Match; AND THEN,
- one that displays the Student ID from the 'New Students' tab/file if the ID from 'New Students' is equal to the ID from 'Registrations'. If no match then display no match.
File: https://drive.google.com/file/d/0BxvqEMoNpMLibGRzaExMelN3SjA/view?usp=sharing
The goal is to identify:
- How many students who were new took classes during the registration period and how many courses
- How many students who graduated took classes during the registration period and how many courses
