Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.