Filter and not in filter

[MrMike]

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. 

Students Missing Essay = CALCULATE(DISTINCTCOUNT(ACTIVITY_LOG[STUDENT_ID]), ACTIVITY_LOG[ACTIVITY_DESCRIPTION] = "Requested Assignment" && ACTIVITY_LOG[STUDENT_ID] NOT IN LIST(ACTIVITY_LOG[STUDENT_ID], ACTIVITY_LOG[ACTIVITY_DESCRIPTION] = "No Essay"))

For example the data would be like this

Student ID - Activity Description

1 - Requested Assignment

1 - No Essay

2 - Requested Assignment

3 - Requested Assignment

3 - No Essay

Then my measure would return 2, because 2 of the 3 students requested but didn't have essay.

[AmedeoM · Accepted 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

[MrMike]

That worked! I verified with several different date ranges and results came back as expected. Thank you.

[Carmichael]

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

[MrMike]

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?

[MrMike]

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 Assignment

1 - No Essay

1 - Recieved Essay

2 - Requested Assignment

2 - Recieved Essay

3 - Requested Assignment

3 - No Essay

So in this example the measure would return 1 because 2 out of the 3 students eventually send in an essay.

[AlexisOlson]

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

[MrMike]

That won't work because some students are in the Activity Log and havn't requested assignment yet.

For example

Data example:

1 - Login

1 - Requested Assignment

1 - No Essay

1 - Recieved Essay

1 - Logout

2 - Login

2 - Requested Assignment

2 - Recieved Essay

3 - Login

3 - Requested Assignment

3 - No Essay

4 - Login

4 - Logout

So 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.

[AlexisOlson]

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

[MrMike]

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."

[Martin125]

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

[AlexisOlson]

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.