Grouped filters in a calculate function

[max_bradley]

Hi all,

I'd like to caclulate something called persistent absence (PA) for a school. This is defined as the percentage of students that are absent 10% of the time or more. 

This can be done very simply by adding a calculated column to the students table, calculating their attendance and doing a measure that divides those for whom it is <=90% by the total number of students. 

However, I'd like to add a date filter to a report with PA on it, and some students may be PA one year, and not PA the next. To do this, I'd need a measure that will count the number of students with attendance <=90% over the filtered period, so a calculated column won't work. I've tried;

PAStdCnt = CALCULATE(
    DISTINCTCOUNT(StdBasic[SchoolAdno]),
    [Att%]<=0.9
)

Where;

Att% = DIVIDE(
    CALCULATE(
    COUNT(AttbyDate[StatMeaning]),
    OR(
        AttbyDate[StatMeaning]="Present",
        AttbyDate[StatMeaning]="Approved Educational Activity"
    )
),
    CALCULATE(
    COUNT(AttbyDate[StatMeaning]),
    AttbyDate[StatMeaning]<>"Attendance not required"
)
)

But of course the calculate measure doesn't recognise that it is calculating this measure per student. How can I approach this so that the attendance is filtered by student when in the calculate function in the measure?

Thanks in advance,

[v-henryk-mstf]

Hi @max_bradley ,

Can you provide the relevant test data and screenshots of the expected results and describe them so that I can answer them for you as soon as possible?

Looking forward to your reply.

Best Regards,
Henry

[max_bradley]

Hi @v-henryk-mstf ,

Thanks for getting back to me. Some dummy data can be found here that should replicate these figures;

The measures in this table are 

2122PA% = DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(StdBasic[SchoolAdno]),
        StdBasic[2122PA]=TRUE,
        NOT(ISBLANK(StdBasic[2122PossCnt]))
    ),
    CALCULATE(
        DISTINCTCOUNT(StdBasic[SchoolAdno]),
        NOT(ISBLANK(StdBasic[2122PossCnt]))
    )
)

2223PA% = DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(StdBasic[SchoolAdno]),
        StdBasic[2223PA]=TRUE,
        NOT(ISBLANK(StdBasic[2223PossCnt]))
    ),
    CALCULATE(
        DISTINCTCOUNT(StdBasic[SchoolAdno]),
        NOT(ISBLANK(StdBasic[2223PossCnt]))
    )
)

What I'm trying to achieve is to remove the need for two seperate calculated columns in the StdBasic dataset, and instead calculate this across a measure. This should allow me to view these two values by changing the values in a slicer associated with the table visual.

[max_bradley · Accepted solution]

Just as an update on this for anyone who follows along. A working solution is available here

PersistentAbs = 
countrows(
filter(
students,
and(
calculate(
StudentAttendances[Attendance%])<.9,
isnumber(
calculate(
StudentAttendances[Attendance%]
)))))