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
Anonymous
Not applicable

DAX Run out of Memory

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

6 REPLIES 6
AlB
Super User
Super User

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 

SU18_powerbi_badge

 

Anonymous
Not applicable

@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

@Anonymous ,

 

Can you share what is the expected output ?

 

Regards,

HN

Anonymous
Not applicable

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

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
Not applicable

@lbendlin 

 

Thanks for the advised

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.

Top Solution Authors