True/False indicator based on a measure?

[amberebee]

Hi everyone,

I have tried searching and I don't think I have the right vocabulary to find just what I need, so I hope you can help me!

I have data like the attachment (well, I don't see anywhere to add an attachment, so I'll come right back after I post this). I have the following measure:

AbsRate = calculate(COUNT(table[DATE]), filter(table, table[Attendance]="Absent"))/calculate(COUNT(table[DATE]))

So, depending on the context, AbsRate would show the percentage of absences in an entire school, grade level, or individual student.

I need to figure out how many students (unique StudentID) have an AbsRate of 10% or higher. I might end up with 3 measures:

• Total number of students = count unique studentID
• Total number of students chronically absent = count unique studentID if absrate >=.10
• Percentage chronically absent = total number of students chronically absent / total number of students.

Can anyone help me create these measures based on this data format?

[amberebee]

https://drive.google.com/file/d/1iK84pkUeAh7jBwEw7xWRiosgPoPnVdgx/view?usp=sharing 

OK, let's see if this works.

I apologize for not including the school variable in my initial question. I didn't realize it mattered. 

Here's what you will see in the linked report. AbsRate is calculating properly in the top table, but the # and % of students chronically absent does not vary by school the way I want. School A has 2 students, 67% chronically absent. School B has 3 students, 100% chronically absent.

It does seem like % chronically absent is fine, it's just the count of students chronically absent that is messing up the %. It's dividing 5 (number of chronically absent students in the whole division) by 3 (number of students in a particular school)

[amberebee]

StudentID	Date	Attendance
11111	1/1/2023	Present
11111	1/2/2023	Present
11111	1/3/2023	Present
11111	1/4/2023	Present
11111	1/5/2023	Present
22222	1/6/2023	Present
22222	1/7/2023	Present
22222	1/8/2023	Present
22222	1/9/2023	Present
22222	1/10/2023	Absent
33333	1/11/2023	Present
33333	1/12/2023	Present
33333	1/13/2023	Absent
33333	1/14/2023	Absent
33333	1/15/2023	Absent

I don't think I can attach a file, so here's how the data look. Thanks!

[Sahir_Maharaj]

Hello @amberebee, 

1. Total number of students (count unique StudentID):

Total number of students = DISTINCTCOUNT(table[StudentID])

2. Total number of students chronically absent (count unique StudentID if AbsRate >= 0.10):

Total number of students chronically absent =
CALCULATE(
    DISTINCTCOUNT(table[StudentID]),
    FILTER(ALL(table), [AbsRate] >= 0.10)
)

3. Percentage chronically absent (Total number of students chronically absent / Total number of students):

Percentage chronically absent = DIVIDE([Total number of students chronically absent], [Total number of students], 0)

[amberebee]

Hi Sahir,

Thanks so much for engaging with my question! Here is the outcome when I create the three measures you provided. Each row represents a different school. The number of students column is correct. I'm still trying to figure out what's going on with the other 2 columns, but roughly speaking, the number of students chronically absent in a school should range from about 8-30% of the total number of students. There's something off about the context of the measures that I haven't figured out yet.

[amberebee]

I replicated your measures in Power BI using the simpler example dataset, and they worked. I'm going to expand the simple dataset to also include different schools to see if I can better replicate the issue in my larger dataset. Stay tuned.