Forum Discussion
Identify Consecutive Data?
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 |
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
3 Replies
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
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.
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
