Identify Consecutive Data?

[Random]

Hi, I've started creating my first report in Power Bi & have managed to create all the visuals & measures I need except for one & would like to know if I can achieve what I want with Dax or whether I would need to reconfigure my data structure.

The report is on student attendance & what I'm trying to create is a visual of students that have been absent on the last 3 marked sessions of a register.  The key data would be in the format below with absent Marks being those with an O.  For example Student 19765 has been absent on the last 3 occassions on register 101145 but not 101146 so I would want to pull out the data for the top 3 rows.  Is this possible via Dax, if so I would appreciate it if someone could give me some indication how!

RegisterNo	SessionDate	StudentRef	Mark
101145	27/11/2018	19765	O
101145	20/11/2018	19765	O
101145	13/11/2018	19765	O
101145	06/11/2018	19765	/
101146	27/11/2018	19765	/
101146	20/11/2018	19765	/
101146	13/11/2018	19765	/
101146	06/11/2018	19765	/
101145	27/11/2018	15432	O
101145	20/11/2018	15432	O
101145	13/11/2018	15432	O
101145	06/11/2018	15432	O

[v-juanli-msft · Accepted solution]

Hi @Random

Add a measure

count_flag = CALCULATE(COUNT(Sheet7[Mark]),FILTER(ALLEXCEPT(Sheet7,Sheet7[StudentRef],Sheet7[RegisterNo]),[flag]=1))

add this measure in the Visual level filter, select "show items when value is 3"

Best Regards

Maggie

[v-juanli-msft]

Hi @Random

Create measures

sessiondate_measure = MAX([SessionDate])

rank =
RANKX (
 FILTER (
 ALL ( Sheet7 ),
 [StudentRef] = MAX ( [StudentRef] )
 && [RegisterNo] = MAX ( [RegisterNo] )
 ),
 [sessiondate_measure],
 ,
 DESC
)


flag = IF(MAX([Mark])="o"&&[rank]<=3,1,0)

Best Regards

Maggie

[Random]

Thank you for your reply, your suggestion successfully identifies the absence marks that occur at any point in the last 3 weeks & so picks up those with 3 consecutive absences for the same student on the same register (3 in a row) but also includes students that have only had 1 or two absences that I did not want.  Sorry if my original post didn't make this clear.  I assume this would be much easier if I just pivot the attendance data or is this still possible with Dax?  Either way thanks for your suggestion.