Ignore slicer in summarize

[elJukes]

Hi there

I'm trying to calculate how many people have achieved above a certain score across a set of subjects and have run into a problem. I'm trying to use summarize to create a temporary table that counts the number of times a student gets above a certain score:

test_score_over_3 = 

    VAR count_of_grades = 
        SUMMARIZE(
           FILTER(Tests, [Points] > 3), 
           Tests[StudentId], 
           "Count", 
           COUNT(Tests[Points])
        )

    VAR percentage = COUNTROWS(FILTER(count_of_grades, [Count] = 3)) / [pupil_count]
    RETURN

    IF([pupil_count] = 0, "-", IF(ISBLANK(percentage), 0, percentage))

In the main this works well, but I have come up against a problem when trying to do a particular type of calculation. The Tests table is filtered using slicers on two dimension tables.

The first table is a student details table, which allows us to filter down students in a particular grade. This is linked to the Tests table on the StudentId column.

The second table is a table containing the term which the Test was taken. It's linked to the Tests table on the TermId column.

For the calculaton I'm currently working on I need to override the filter from the slicer on the term table, but retain the filter from the slicer on the student table. Is there a straightforward way of adapting the measure above to accept a different TermId to the one currently being filtered on?

[v-chuncz-msft · Accepted solution]

@elJukes ,

You may try using CALCULATETABLE.

FILTER ( CALCULATETABLE ( Tests, ALL ( Term[TermId] ) ), [Points] > 3 )