Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
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.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |