Forum Discussion

dphillips's avatar
dphillips
Icon for Helper IV rankHelper IV
2 years ago
Solved

Measure using filters on fields from related table

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 ...
Need Help
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    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's avatar
dphillips
Icon for Helper IV rankHelper IV
2 years ago

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..