topic Re: Filter and not in filter in DAX Commands and Tips

Filter and not in filter

MrMike — Fri, 19 Feb 2021 21:59:52 GMT

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.

Re: Filter and not in filter

MrMike — Fri, 19 Feb 2021 22:04:47 GMT

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.

Re: Filter and not in filter

AlexisOlson — Fri, 19 Feb 2021 22:14:32 GMT

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

Re: Filter and not in filter

MrMike — Sat, 20 Feb 2021 15:31:48 GMT

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.

Re: Filter and not in filter

Carmichael — Sat, 20 Feb 2021 16:20:57 GMT

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

Re: Filter and not in filter

MrMike — Thu, 25 Feb 2021 15:37:23 GMT

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?

Re: Filter and not in filter

AlexisOlson — Thu, 25 Feb 2021 22:13:40 GMT

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

Re: Filter and not in filter

MrMike — Thu, 25 Feb 2021 21:48:12 GMT

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

Re: Filter and not in filter

AlexisOlson — Thu, 25 Feb 2021 22:13:06 GMT

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.

Re: Filter and not in filter

AmedeoM — Thu, 25 Feb 2021 22:19:26 GMT

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

Re: Filter and not in filter

MrMike — Fri, 26 Feb 2021 20:29:16 GMT

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

essay

Martin125 — Wed, 14 Dec 2022 21:03:58 GMT

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