Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Random
Frequent Visitor

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!

 

RegisterNoSessionDateStudentRefMark
10114527/11/201819765O
10114520/11/201819765O
10114513/11/201819765O
10114506/11/201819765/
10114627/11/201819765/
10114620/11/201819765/
10114613/11/201819765/
10114606/11/201819765/
10114527/11/201815432O
10114520/11/201815432O
10114513/11/201815432O
10114506/11/201815432O
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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"

15.png

 

Best Regards

Maggie

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

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"

15.png

 

Best Regards

Maggie

v-juanli-msft
Community Support
Community Support

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)

1.png

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.