Forum Discussion
Helper IIFilter and not in filter
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
Advocate IIIAs 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!!
Helper III 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?