Forum Discussion

MrMike's avatar
MrMike
Icon for Helper II rankHelper II
5 years ago
Solved

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[STUD...
Help required
  • AmedeoM's avatar
    AmedeoM
    5 years ago

    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's avatar
MrMike
Icon for Helper II rankHelper II
5 years ago

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's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      MrMike
      Icon for Helper II rankHelper II
      5 years ago

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        5 years ago

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