Forum Discussion
Filter and not in filter
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.
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
11 Replies
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:
1 - Requested Assignment1 - No Essay1 - Recieved Essay2 - Requested Assignment2 - Recieved Essay3 - Requested Assignment3 - No EssaySo in this example the measure would return 1 because 2 out of the 3 students eventually send in an essay.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:
1 - Login1 - Requested Assignment1 - No Essay1 - Recieved Essay1 - Logout2 - Login2 - Requested Assignment2 - Recieved Essay3 - Login3 - Requested Assignment3 - No Essay4 - Login4 - LogoutSo in this example student #4 logged in but didn't request assignment. There are other activities but I don't want to confuse you. So I really need a "NOT IN" filter.
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?
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 _ResultThat worked! I verified with several different date ranges and results came back as expected. Thank you.
