cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## True/False indicator based on a measure?

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?

5 REPLIES 5
New Member

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)

New Member
 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!

Super User

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)``

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
New Member

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.

New Member

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.