In PowerBI I have two tables - a student table (DIM) containing student demographic data such as grade level, gender, etc, and a table listing the many Grades, GPA, etc. the students have taken over many different terms (Facts) (3500+ Entries). I want to report the GPA for the most current term a student has completed. I have created a column with a numeric value for the terms to allow me to try and find the maximum.
How can I find the maxiumum Term Value and report the GPA for that term value by Student ID? I want to use the results in pivot tables that reference the Student table so that I can report GPA by gender, race, etc.
I think this would be like a table of sales by and trying to find the most recent month a salesman they sold something and the value of the sale. I am imagining that there is a MAX function involved. I have created queries to get me to this number, and could use that query in the data model, but I think I may be creating duplicated fact tables by doing it that way.
Thank you.
Cheryl Ulsh
Hi Cheryl,
Is it possible to have more than 1 term value at the same date? In this case, a measure will return more than 1 value for the latest date, we might need another approach.
Please upload a small example of those 2 tables, will be easier to understand the exact situation.
Sorry for the slow reply. I've attached the data set. what I have is multiple entries for each student ID, and I just want to report the most recent term's GPA. The Term Value is created to be able to find the greatest value. As I stated before, I am currently using a separate query to get the greatest term value by grouping. It just seems like an extra and unnecessary step.
Hi Cheryl,
Please take a look at the attached file and let me know if this is what you're after.
Yes, the outcome is what I would expect. I see you added a custom column, but can you explain what that is doing?
It's the term value converted to a number that can be used in calculations and comparisons (MAX, <>)
Thanks for both the formula and the measure. Now I need to try and learn more about the filter and all functions. But I certainly can use this. Thank you!
