Measure using filters on fields from related table

[dphillips]

I have a 'Students' table which has a unique set of students. The fields include a StudentID, Name, Yearlevel, MentorGroup and Fileyear. I then have a related table called 'Merits' which has all the merits a student has received over a number of years. They are linked in a one to many join on the StudentId. The related merit table also has a date column which I want to filter to provide only merits for the current year. I have two filters on the page - a filter for a students MentorGroup and a filter for the Name of the student. When I select a student, I want to get back how many merits the best student in that YearLevel has received for this year only. For example, if I select a 'MentorGroup' called '08MENTJN' and the student 'John Smith', who is in Year 8, I want to know how many merits the best student in Year 8 has received this year. I am doing this so that I can compare how many merits the student selected has received when compared to the student who has received the most. I would also like to know what the average number of merits for that YearLevel for the current year are. This would also help me to compare where the selected students is in relation to the average.

At the moment I have created a measure like this just to see if I can get the total number of merits by students in the selected student's Year Level for this year.

MeritsWholeCohort =

VAR SelectedStudentYear = SELECTEDVALUE(vStudents[StudentYearLevel])

VAR SelectedStudentFileYear = SELECTEDVALUE(vStudents[FileYear])

RETURN

CALCULATE( COUNTROWS('Merits All'), FILTER( 'Merits All', RELATED(vStudents[StudentYearLevel]) = SelectedStudentYear && 'Merits All'[IncidentDate] = SelectedStudentFileYear ), ALL(vStudents[StudentID]) // Remove filter on individual student, filter on entire year group )

For some reason, it still seems to only return the student selected, not all of the students in the YearLevel. I was then going to use this measure to look for 1. the student with the most merits and then 2. The average number of merits for the whole YearLevel.

If anyone could help me to create those 2 measures I woud be very grateful.

[gmsamborn · Accepted solution]

Hi @dphillips 

Would these measures help?

Avg = 
    AVERAGEX(
        FILTER(
            ALL( 'Student' ),
            'Student'[Fileyear] = MAX( 'Student'[Fileyear] )
                && 'Student'[YearLevel] = MAX( 'Student'[YearLevel] )
        ),
        [TotalMeritsThisYear]
    )



MaxStudent = 
    MAXX(
        FILTER(
            ALL( 'Student' ),
            'Student'[Fileyear] = MAX( 'Student'[Fileyear] )
                && 'Student'[YearLevel] = MAX( 'Student'[YearLevel] )
        ),
        [TotalMeritsThisYear]
    )

On Page 2, you'll notice that [Avg] and [MaxStudent] change as [YearLevel] changes.  Also, you can compare the numbers to those on Page 1 where you can set slicers.

TestMerits-3.pbix

[dphillips]

Thanks  @gmsamborn for having a look at this. Your measures work if the filters for FileYear and Yearlevel are manually selected using filters on that page. I want the Year level and fileyear to be extracted from the Students table without having to explictly set these values. For example, when I select a student from the student table, I want it to pick up the YearLevel for that student and then return the number of merits for the student with the most merits in that YearLevel (for the current year) and another measure returning the average number of merits for that YearLevel (again in the curent year). This info then filters the merits table to return the correct response. I hope that makes sense. 

[gmsamborn · Accepted solution]

Hi @dphillips 

Would these measures help?

Avg = 
    AVERAGEX(
        FILTER(
            ALL( 'Student' ),
            'Student'[Fileyear] = MAX( 'Student'[Fileyear] )
                && 'Student'[YearLevel] = MAX( 'Student'[YearLevel] )
        ),
        [TotalMeritsThisYear]
    )



MaxStudent = 
    MAXX(
        FILTER(
            ALL( 'Student' ),
            'Student'[Fileyear] = MAX( 'Student'[Fileyear] )
                && 'Student'[YearLevel] = MAX( 'Student'[YearLevel] )
        ),
        [TotalMeritsThisYear]
    )

On Page 2, you'll notice that [Avg] and [MaxStudent] change as [YearLevel] changes.  Also, you can compare the numbers to those on Page 1 where you can set slicers.

TestMerits-3.pbix

[dphillips]

That works - thanks so much for the solution. 

[gmsamborn]

You're welcome.  I'm glad it worked.

[dphillips]

Here is the link to a test file I have created - https://drive.google.com/file/d/1TSYWRkySRBcJY3tQ_6imU6ITk9ZToywt/view?usp=sharing

Say you select a mentor group of '08MENTBA'. then select a student - 'Asa Strickland'. This student is in Year 8 in the current FileYear of 2024.

1. I would like to get a measure which gives me the number of merits for the student with the most merits in the Selected YearLevel (Year 8 but only for the the current FileYear which is 2024 in this case. (It should be 14 merits - I created a graph to show this in the second tab. Name = Ruqayyah Osborn)

2. I would also like to get a measure which gives me the average number of merits a student has received in the selected YearLevel (Year 8 and again only for the selected FileYear (2024) This should be 4.2 merits on average. Again, see second tab.

Please ignore the formula I posted originally. It does not do what I want and is giving me the wrong info anyway. Not sure how to create the two measures I really need.

Hope this helps and thanks for any help you can give..

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result very clearly.  Share the download link of the PBI file.

[dphillips]

Hello @Ashish_Mathur . Just wondering if you had a solution for these measures? Would be very grateful if you did.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.  I get 14 as the answer in the card visual.

Hope thish helps.

[dphillips]

Thanks for taking the time to have a look a this. Very much appreciated.

[Ashish_Mathur]

You are welcome.

[gmsamborn]

Hi @dphillips 

I kept your [TotalMeritsThisYear] as is.

TotalMeritsThisYear = 
    CALCULATE(
        COUNT( Merits[StudentID] ),
        YEAR( Merits[IncidentDate] ) = SELECTEDVALUE( Students[Fileyear] )
    )

Then I used that measure twice.  First in a MAXX to find the student with the most.

MaxStudent = 
    MAXX(
        'Students',
        [TotalMeritsThisYear]
    )

And then in AVERAGEX.

Avg = 
    AVERAGEX(
        'Students',
        [TotalMeritsThisYear]
    )

I hope I understood correctly.

TestMerits-2.pbix