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.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!