cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrMike
Helper II
Helper II

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. 

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.
1 ACCEPTED SOLUTION
AmedeoM
Regular Visitor

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

View solution in original post

11 REPLIES 11
AmedeoM
Regular Visitor

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.

Carmichael
Resolver I
Resolver I

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?

MrMike
Helper II
Helper II

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.

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

 

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.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors