DAX Help for measure

[Anonymous]

Hi,

I do not know how to create the DAX for direct measure of the total number ChildID who has absent 3 or more consecutive days. Maybe there is any simple DAX to measure this?

Please help me to do this and thank you for your help.

I am sorry for the tag, but please help me find out DAX to measure:
@harshnathani @darlove  @Greg_Deckler  @mahoneypat @camargos88 @amitchandak 

@vivran22 @bboobe @LAndes  @AlB  @Pragati11  @parry2k @az38  @edhans  

Here, I am attached the data on this link:

My Data 

[mahoneypat]

I'm sure there is a more efficient/elegant way to do this, but I ran out of time on my lunch break.  This expression gets the right result of 1 for the sample dataset provided.

Kids with 3+ Consecutive Days Absent =
VAR summarytable =
ADDCOLUMNS (
SUMMARIZE ( Attendance, Attendance[ChildID], Attendance[AttendanceDate] ),
"inarow", CALCULATE (
VAR thisdate =
SELECTEDVALUE ( Attendance[AttendanceDate] )
VAR falselast3days =
CALCULATE (
COUNT ( Attendance[ChildID] ),
ALLEXCEPT ( Attendance, Attendance[ChildID] ),
Attendance[AttendanceDate] <= thisdate,
Attendance[AttendanceDate] >= thisdate - 2,
Attendance[IsPresent?] = FALSE ()
)
RETURN
falselast3days
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( Attendance[ChildID] ),
FILTER ( summarytable, [inarow] = 3 )
)

Your dates were all consecutive, so this works.  I suspect you are going to need to exclude non-school days, but you can adapt this approach.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat ,

I have not running that DAX because DAX running to slow. Please share pbix file on attachment.

Best,

LA

[Greg_Deckler]

@Anonymous - I did it this way, (PBIX attached after sig)

Created this column:

Absent = 
    VAR __Last = MAXX(FILTER('AttendanceMaster',[ChildID] = EARLIER([ChildID]) && [AttendanceDate] < EARLIER([AttendanceDate])),[AttendanceDate])
    VAR __Min = MINX(FILTER('AttendanceMaster',[ChildID] = EARLIER([ChildID])),[AttendanceDate])
RETURN
    IF([AttendanceDate] = __Min,0,([AttendanceDate] - __Last) * 1.)

And then this measure:

Measure = 
    COUNTROWS(
        SUMMARIZE(
            FILTER('AttendanceMaster',[Absent] >= 3),
            [ChildID]
        )
    )

[Anonymous]

@Greg_Deckler ,

Thank you for the DAX Formula,

I think this DAX not measured total ChildID who has FALSE 3+ days on column IsPresent.

For example, please look at the table below:

ChildID	AttendanceDate	IsPresent?
11	3 February 2020	FALSE
11	4 February 2020	FALSE
11	5 February 2020	FALSE
11	6 February 2020	FALSE
22	3 February 2020	TRUE
22	4 February 2020	FALSE
22	5 February 2020	FALSE
22	6 February 2020	TRUE

Based on the table, we know that there is 1 ChildID was absent for 4 consecutive days, the ChildID is 11.

I am sorry to make you busy.

Thank you

[Greg_Deckler]

@Anonymous - Sorry, missed the IsPresent field and was just going by AttendanceDate. Clearly misunderstood the data. Will take another look.

[Greg_Deckler]

@Anonymous  - Went back to my EARLIER approach (sorry bad joke) and implemented Cthulhu. PBIX is attached.

Absent = 
    VAR __index = CALCULATE(MAX([AttendanceDate]))
    VAR __group = CALCULATE(MAX([ChildID]))
    VAR __tmpTable1 = FILTER(ALL('AttendanceMaster'),[ChildID]=__group && [IsPresent] = FALSE() && [AttendanceDate]<__index)
    VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[AttendanceDate] - MAXX(FILTER(ALL('AttendanceMaster'), [IsPresent] = FALSE() && [AttendanceDate] < EARLIER([AttendanceDate]) && [ChildID]=EARLIER([ChildID])),[AttendanceDate])) 
    VAR __max = MAXX(__tmpTable2,[AttendanceDate])
    VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[AttendanceDate])
    VAR __tmpTable3 = FILTER(__tmpTable2,[AttendanceDate]>=__maxStart)
RETURN 
    IF(ISBLANK(__max),1,IF(__max=__index-1,COUNTROWS(__tmpTable3)+1,1))

[Anonymous]

Hi @Greg_Deckler ,

Thank you.

I found this DAX works well but there is a little joke from you when I have looked at Absent Column on Data Table.  We know that If Column IsPresent is TRUE than Column Absent will be 0.

DAX that you have created is very simple and I understand your approach and I think this a very cool way.

@mahoneypat ,

Thanks,

I will be looking for your approach now.

Best

LA