DAX Run out of Memory

[Anonymous]

Hello,

I have run this DAX before

First date Child was absent consecutive days = 
IF (
    NOT AttendanceMaster[IsPresent],
    VAR NextPrecense_ =
        CALCULATE (
            MIN ( AttendanceMaster[AttendanceDate] ),
            ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
            AttendanceMaster[AttendanceDate] > EARLIER ( AttendanceMaster[AttendanceDate] ),
            AttendanceMaster[IsPresent] = TRUE ()
        )
    VAR LastAbsenceDay_ =
        CALCULATE (
            MAX ( AttendanceMaster[AttendanceDate] ),
            ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
            AttendanceMaster[AttendanceDate] < NextPrecense_
        )
    VAR NumConsecutiveAbsentDays_ =
        CALCULATE (
            COUNT ( AttendanceMaster[AttendanceDate] ),
            ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
            AttendanceMaster[AttendanceDate] >= EARLIER ( AttendanceMaster[AttendanceDate] ),
            AttendanceMaster[AttendanceDate] < NextPrecense_
        )
    VAR IsStartofAbsence =
        VAR PreviousDate_ =
            CALCULATE (
                MAX ( AttendanceMaster[AttendanceDate] ),
                ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
                AttendanceMaster[AttendanceDate] < EARLIER ( AttendanceMaster[AttendanceDate] )
            )
        RETURN
            IF (
                ISBLANK ( PreviousDate_ ),
                TRUE (),
                CALCULATE (
                    DISTINCT ( AttendanceMaster[IsPresent] ),
                    ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
                    AttendanceMaster[AttendanceDate] = PreviousDate_
                )
            )
    RETURN
        IF ( IsStartofAbsence, AttendanceMaster[AttendanceDate] )
)

and then I have run this DAX to created First Day Child Present after Absent:

First Date Child was Present after absent consecutive days = 
IF (
    NOT AttendanceMaster[IsPresent],
    VAR NextPrecense_ =
        CALCULATE (
            MIN ( AttendanceMaster[AttendanceDate] ),
            ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
            AttendanceMaster[AttendanceDate] > EARLIER ( AttendanceMaster[AttendanceDate] ),
            AttendanceMaster[IsPresent] = TRUE ()
        )
    VAR LastAbsenceDay_ =
        CALCULATE (
            MAX ( AttendanceMaster[AttendanceDate] ),
            ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
            AttendanceMaster[AttendanceDate] < NextPrecense_
        )
    VAR NumConsecutiveAbsentDays_ =
        CALCULATE (
            COUNT ( AttendanceMaster[AttendanceDate] ),
            ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
            AttendanceMaster[AttendanceDate] >= EARLIER ( AttendanceMaster[AttendanceDate] ),
            AttendanceMaster[AttendanceDate] < NextPrecense_
        )
    VAR IsStartofAbsence =
        VAR PreviousDate_ =
            CALCULATE (
                MAX ( AttendanceMaster[AttendanceDate] ),
                ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
                AttendanceMaster[AttendanceDate] < EARLIER ( AttendanceMaster[AttendanceDate] )
            )
        RETURN
            IF (
                ISBLANK ( PreviousDate_ ),
                TRUE (),
                CALCULATE (
                    DISTINCT ( AttendanceMaster[IsPresent] ),
                    ALLEXCEPT ( AttendanceMaster, AttendanceMaster[ChildID] ),
                    AttendanceMaster[AttendanceDate] = PreviousDate_
                )
            )
    RETURN
        IF ( IsStartofAbsence, LastAbsenceDay_+1 )
)

But the second DAX does not work well because it runs out of memory.

Please help me to fix this problem.

@AlB Please help me again. I am sorry

Thank you before.

Best,
LA

[AlB]

Hi @Anonymous 

I'm not following. I believe you posted the same question a few days ago and you got several suggestions to make the code less resource-intensive from other people. You marked one of the suggestions (or several, I don't remember) as solution. So what is the point of posting the very same question all over again?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

I am sorry, I have to post it again because I  have not found any solution.

I am sorry for that, but please help me to fix this.

Thanks before

Best

Lorens

[harshnathani]

@Anonymous ,

Can you share what is the expected output ?

Regards,

HN

[Anonymous]

The expected output I will use this DAX formula to get the number of days that the child have absent consecutive days.

Number of Consecutive days = 
VAR TBL_Date =
    CALENDAR ( AttendanceMaster[First date of Consecutive absence] , AttendanceMaster[Last date Consecutive of absence] )

VAR TBL_FinalDate =
    ADDCOLUMNS ( TBL_Date,
    "AbsentDays", IF ( WEEKDAY ( [Date] , 3 ) >= 6 , 0 , 1 ),
    "Holidays", IFERROR ( LOOKUPVALUE ( Holiday[Holiday Count], Holiday[Date] , [Date] ), 0 )
    )

RETURN
    SUMX ( TBL_FinalDate , IF ( [AbsentDays] = 1 , 1 , 0 ) && [Holiday] = 0 , 1 , 0 )
    )

Thanks

[lbendlin]

The expected output will use a DAX formula that is optimized for the problem.  You can use DAX Studio to troubleshoot your query performance and remove any unneeded context transitions and extra record loops. Consider using variables.

[Anonymous]

@lbendlin 

Thanks for the advised