Forum Discussion

Random's avatar
Random
Frequent Visitor
7 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
Random
Frequent Visitor
7 years ago

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.