Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
What is the DAX synax to count how many students requested but didn't supply the essay.
I tried this but I know its is wrong.
Solved! Go to Solution.
Hello,
please try this measure and let me know if it works:
Measure :=
VAR _StudentsWithoutEssay =
CALCULATETABLE (
VALUES ( ACTIVITY_LOG[STUDENT_ID] ),
ACTIVITY_LOG[ACTIVITY_DESCRIPTION] = "No Essay"
)
VAR _Result =
CALCULATE (
DISTINCTCOUNT ( ACTIVITY_LOG[STUDENT_ID] ),
ACTIVITY_LOG[ACTIVITY_DESCRIPTION] = "Requested Assignment",
NOT ACTIVITY_LOG[STUDENT_ID] IN _StudentsWithoutEssay
)
RETURN
_Result
Hello,
please try this measure and let me know if it works:
Measure :=
VAR _StudentsWithoutEssay =
CALCULATETABLE (
VALUES ( ACTIVITY_LOG[STUDENT_ID] ),
ACTIVITY_LOG[ACTIVITY_DESCRIPTION] = "No Essay"
)
VAR _Result =
CALCULATE (
DISTINCTCOUNT ( ACTIVITY_LOG[STUDENT_ID] ),
ACTIVITY_LOG[ACTIVITY_DESCRIPTION] = "Requested Assignment",
NOT ACTIVITY_LOG[STUDENT_ID] IN _StudentsWithoutEssay
)
RETURN
_Result
That worked! I verified with several different date ranges and results came back as expected. Thank you.
As an easy alternative can you have a table where you just pivot the activity descriptions (only the ones that you need) into columns.
from what you mention you might only need 2 columns pivoted
1. Requested
2. submitted
Then you have option of adding a conditional column in PQ to flag students who have requstwd but not submitted or you can calculate that using dax
VAR _count = countrows(activity_log)
RETURN
caculate(
_count,
Activity_log[requested]<> null && isblank(activity_log[submitted]
)
*code written in back of cab so possible error.... but I hope you get my drift*
one question to better understand context. Will the same student have multiple essays where this needs to be checked? If so then a unique identifier for the assignment also needs to be included so you have granularity and a studentID-AssigmentID level
Did I answer your question? Mark my post as a solution if I did! Like it if it helped. Appreciate the Kudos!!
I tired your suggestion but that caused more errors.
Are you saying there is no "NOT IN" function in Power BI so I have to create a pivot table?
Is there some way to use my SQL that has a "NOT IN" in the Power BI data source?
I forgot there is a 3 activity. "Recieved Essay" so I want to also filter out students who at first had no essay then later sent in assay.
Data example:
How about taking the total number of students and subtracting the ones that have a "Received Essay" status?
Students Missing Essay =
DISTINCTCOUNT ( ACTIVITY_LOG[STUDENT_ID] )
- CALCULATE (
DISTINCTCOUNT ( ACTIVITY_LOG[STUDENT_ID] ),
ACTIVITY_LOG[ACTIVITY_DESCRIPTION] = "Received Essay"
)
That won't work because some students are in the Activity Log and havn't requested assignment yet.
For example
Data example:
OK. I think you'd want something like this then:
Students Missing Essay =
SUMX (
VALUES ( ACTIVITY_LOG[STUDENT_ID] ),
VAR Descriptions = CALCULATETABLE ( VALUES ( ACTIVITY_LOG[ACTIVITY_DESCRIPTION] ) )
RETURN
IF (
"Requested Assignment" IN Descriptions
&& NOT ( "Recieved Essay" IN Descriptions ),
1,
0
)
)
I tired your example and I get a syntex error on both Descriptions words. The error says "The function expects a table expression for argument '', but a string or numeric expression was used."
Essays can be a rewarding and challenging type of writing and are often assigned either to be done in class, which requires previous planning and practice (and a bit of creativity) on the part of the student, or as homework, which likewise demands a certain amount of preparation
That's what I get for not testing...
It should be CALCULATETABLE instead of CALCULATE and I failed to preserve your misspelling of "Recieved".
I will edit it momentarily.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
17 | |
16 |
User | Count |
---|---|
29 | |
27 | |
20 | |
15 | |
14 |