Some help required with a measure

[Simon_Evans]

Hi Everyone

I'm a little bit stumped with what I think should be quite easy but I'm not sure how to achieve it.

Here is what I need. I need a measure that counts the number of students where their attendance is less than or equal to 0.90.

Below is a sample from the Attendance Table which contains the date, Student ID and Attendance code for every date the student can possibly attend school. This contains upwards of 100,000 rows.

I have a measure that counts the number of present attendance codes.

Total Present Marks = CALCULATE(COUNTROWS(SessionAttendance),SessionAttendance[Attendance Code]="A1329183376" || SessionAttendance[Attendance Code]= "A1161584171" || SessionAttendance[Attendance Code]="A830070477" || SessionAttendance[Attendance Code]="A186523258" || SessionAttendance[Attendance Code]="A1529934786"|| SessionAttendance[Attendance Code]="A319327454"|| SessionAttendance[Attendance Code]="A1500139297"|| SessionAttendance[Attendance Code]="A1601102008")

and another that counts the total possible sessions.

Total Marks = CALCULATE(COUNTROWS(SessionAttendance),SessionAttendance[Attendance Code]<>"A930902628" && SessionAttendance[Attendance Code]<>"A1801476586" && SessionAttendance[Attendance Code]<>"A1131916182" && SessionAttendance[Attendance Code]<>"A1299388415" && SessionAttendance[Attendance Code]<>"A1462380788" && SessionAttendance[Attendance Code]<>"" && SessionAttendance[Attendance Code]<>"A124965801" && SessionAttendance[Attendance Code]<>"A493975360" && SessionAttendance[Attendance Code]<>"A795820045" && SessionAttendance[Attendance Code]<>"A627303844" && SessionAttendance[Attendance Code]<>"A996587839" && SessionAttendance[Attendance Code]<>"A323305093" && SessionAttendance[Attendance Code]<>"A700043804")

To work out the Percentage attendance it is simply (Total Present Marks / Total Marks) and round up to 2 decimal places.

How can I use DAX to now create the measure that counts the number of students within the table where the percentage attendance is less than equal to 0.90 please? Any help would be greatly appreciated please.

[Anonymous]

Assuming any Attendance code will do, you could start by simplyfying your formula by using SessionAttendace[AttendanceCode]<>NULL().

My idea is:

Attendance Rate=
    DIVIDE(
        CALCULATE(
            COUNT('SessionAttendance'[AttendanceCode]),
            FILTER(SessionAttendance, [AttendanceCode]<>NULL()) //filter for sessions attended only
        )                                                       //------------------------------------
        COUNT('SessionAttendance'[AttendanceCode]),             //I'm assuming there's no condition on
        2                                                       //what you consider as "Valid session"
    )                                                           //for your statistics

and then

lessThan90=
    CALCULATE(
        DISTINCTCOUNT('SessionAttendance'[StudentID]),
        FILTER('SessionAttendance', [Attendance Rate]<=0.9)
    )

Let me know if this helps. 

[Simon_Evans]

Hi @Anonymous 

Thank you for this, much appreciated.

There are specific codes that need count towards what is an attended session and what is a possible session hence why two measures are very specific.

I like your idea and have tried it however the part where it just seems to not work is the Filter on the table for the attendance being less than 0.90.

Your idea comes back with a count of 449 distinct pupils which is exactly the number of unique student ID's of that table. What I'm aiming for is 33 (I've done a manual count).

If I was to create a calculated table which gives me each student with their own row and summaries of what there attendance is and if it is below 0.90 would that calculated table keeps itself up to date each time the date set is refreshed to bring in the latest attendance marks?

[Simon_Evans]

Ok scrap the idea of calculated table as that won't work as I need this measure to be able to be affected by a Date slicer

[Anonymous]

I'm thinking something like this, although I admittedly don't know whether ADDCOLUMNS() will accept a measure as expression argument.

var t_Attendance=
ADDCOLUMNS('SessionAttendance', "att_Rate", [Attendance Rate])

return
CALCULATE(
    COUNTROWS('t_Attendance'[StudentID]),
    FILTER(att_Rate<=0.9)
)

Let me know how it goes.

[Simon_Evans]

Thank you. I gave that a try but all I got back was error saying the Basetable was missing.

However I did start getting a bit creative about creating a temporary Summarise table and ended up with the following measure:

Perabsent =

var TempTable = SUMMARIZE(SessionAttendance,SessionAttendance[Student ID],"Present Marks",[Total Present Marks],"Possible Sessions",[Total Marks],"Percentage Attendance",[Total Present Marks]/[Total Marks])

var result = CALCULATE(COUNTROWS(TempTable),FILTER(TempTable,[Percentage Attendance] <= 0.90))

RETURN

result

The table it creates looks like this:

Problem is this just counts the number of rows within the temporary table. Again the Calculate function appears to be ignoring the filter. 

Any further ideas welcome but I am also looking at the Evaluate function as that may be able to do this.

Kindest regards

Simon

[v-xiaotang]

Hi @Simon_Evans 

Thanks for reaching out to us.

>>  I need a measure that counts the number of students where their attendance is less than or equal to 0.90.

try

attendance=Total Present Marks / Total Marks

then set it to 2 decimal places.

rows= calculate(countrows('table'),filter(all('table'), [attendance]<=0.9))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Simon_Evans]

Hi @v-xiaotang 

Thank you for this, much apprecaited.

It certainy l;ooks a lot easier to read but it comes back with a value of well over 85,000 which is just less than half of all the rows in that table.

Any further ideas on this would be greatly appreciated.