DAX measure help - need to change evaluation context (I think)

[ngaray]

Hello DAX experts (or not complete beginners),

I am trying to analyze conduct data of students at the university where I work. You can see a simplified and fake version of the table below:

Incident ID	Student ID	Charge	Sanction	Incident ID & Student ID
A111	100	Drinking	Drinking class	A111 1
A111	100	Drinking	monetary sanction	A111 1
A112	200	Loud noise	Policies quiz	A112 2
A113	100	Disruptive behavior	Policies quiz	A113 1
A113	100	Disruptive behavior	monetary sanction	A113 1
A113	100	Vandalism	Policies quiz	A113 1
A113	100	Vandalism	monetary sanction	A113 1
A114	300	Drinking	Drinking class	A114 3
A114	300	Drinking	monetary sanction	A114 3
A115	200	Destruction of property	monetary sanction	A115 2
A116	400	Loud noise	Policies quiz	A116 4

I am trying to calculate the recidivism ratio. Recidivism = Students with more than 1 incident / All Students

In the table above, recidivism = 2/4 = 50%.

Numerator -> student 100 & 200.   Denominator -> student 100, 200, 300, & 400

Unfortunately, I am unable to calculate the numerator correctly. This is the measure that I am using:

numerator recidivism =
if([incident distinct count]>1,
    distinctcount('Table'[Student ID]),
    blank()
)

 The result of this measure is 4, however the right number, as mentioned above, is 2 (student 1 & 2). I believe I need to change the evaluation context. Unfortunately, I don't think I grasp the concept of context well enough to fix my measure.

I am attaching a pbix file as I believe this will help you understand the problem better.

folder with pbix doc link

I appreciate any help,

Nathalia

[parry2k]

@ngaray isn't it should be 3/4, (100, 200, & 300), all have more than 1 incident where as 400 as only one incident?

[ngaray]

Hi @parry2k,

Thank you so much for your response. Something that makes my dataset confusing is that an incident can have multiple rows. This is because a student can be charged for multiple reasons and also be sanctioned with multiple sanctions. This is why in the dataset, student 300 shows up in two rows even though the student is only involved in one incident. Student 300 is involved in incident A113 and is charged with drinking and sanction with a monetary penalty and attending a "drinking class". One row shows the first sanction and the other shows the second sanction.

Incident ID	Student ID	Charge	Sanction	Incident ID & Student ID
A113	300	Drinking	Drinking class	A113 300
A113	300	Drinking	monetary sanction	A113 300

I believe I need to create a subset of the table by getting a distinct count of "Incident ID" column (this will fix having multiple rows for any given incident). On that subset, I would then count the "Student ID" column. Finally, if Count(student ID) > 1 then count in the numerator, if not, ignore/blank. Conceptually, I think I know how to get the result, I just don't know how to write the DAX measure.

Ideal Subset table: 

Incident ID	Student ID	Student Count	Student Count > 1
A111	100	2	TRUE
A112	200	2	TRUE
A113	100	2	TRUE
A114	300	1	FALSE
A115	200	2	TRUE
A116	400	1	FALSE

TRUE = 2 (Student 100 and 200)

I hope I answer your question.

Thank you,

Nathalia

[parry2k · Accepted solution]

@ngaray add this measure and test it

recidivism =
VAR __studentwithmorethanoneincident = 
COUNTROWS(
 FILTER
 (
    SUMMARIZE( Table1, Table1[Student ID],  "Count", DISTINCTCOUNT( Table1[Incident ID] ) ), [Count] > 1
)
)
RETURN DIVIDE( __studentwithmorethanoneincident  , DISTINCTCOUNT( Table1[Student ID] ) ) 

[ngaray]

Hi @parry2k ,

Thank you so much for your help. I tested the measure and it works perfectly! I really appreciate your assistance.

In case anyone else is interested in seeing the measure at work, I updated the pbix with the measure.

Best,

Nathalia