Forum Discussion
Creating columns from summarized data in other tables
I have two datasets. One is enrollment information that states whether students that were enrolled in the fall semester are enrolled in the spring. The second lists all of the financial aid that students received each semester for the last ten years. Each type of award has its own line for each student and term. For example, there would be four lines if a student received both merit scholarships and loans in both Fall 2022 and Spring 2022. Student ID's are being used to connect the two datasets. What I want is to create a column in the dataset with enrollment information that states whether or not a student was a merit scholarships recipient in the Fall semester. I am struggling to figure out how to 1) create a column using data from a different dataset, and 2) pull the correct information into the column where there might be multiple rows per student and term.
3 Replies
Hi,
Share some data (in a format that can be pasted in an MS Excel file) and show the expected result.
I figured out that I can concatenate student ID and term and use that variable to match the datasets, so the pairing by student ID AND term isn't really necessary to solve this. The below tables show what I'm trying to do. I need the info from Table 1 to be summarized into Table 2. This is what I'm going for:
Table 1
Student Award A 1 A 2 A 3 B 2 B 4 C 1 C 2 C 3 C 4 Table 2
Student Received Award 1? A Yes B No C Yes Hi,
Drag Student to the row section of the visual. Write this measure and drag it to the visual
Received award 1? = if(calculate(countrows(Data),Data[Award]=1)>0,"Yes","No")
i have assumed that the data type in the award column is numeric. If not, then change 1 to "1" in the measure shared above.
Hope this helps.
