Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Convert excel function to DAX

Hi, 

 

Can somebody help me to convert this excel pivot table function to DAX

 

Measure =COUNTIFS ([Child ID],[@[Child ID]],[Child is Present?], FALSE(),[Attendance Date],"<="&([@[Attendance Date]]+2),[Attendance Date],">="&[@[Attendance Date]]>=3

 

 

Please help me

Help me please
Help Requested
Help with DAX
  • AlB's avatar
    AlB
    6 years ago

    Anonymous 

    This calculated column will give you the date on which the absence ENDS (will return only non-blank on the rows where an absence starts). Do note that the code is practically the same as the previous one, it just retunrs something different (a different VAR). The code calculates quite a number of VARS  so you can use them to return other things you might be interested in (such as the number of consecutive absence days)

     

     

    Last date of absence =
IF (
    NOT AttendanceMaster[IsPresent];
    VAR NextPresence_ =
        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] < NextPresence_
        )
    VAR NumConsecutiveAbsentDays_ =
        CALCULATE (
            COUNT ( AttendanceMaster[AttendanceDate] );
            ALLEXCEPT ( AttendanceMaster; AttendanceMaster[ChildID] );
            AttendanceMaster[AttendanceDate] >= EARLIER ( AttendanceMaster[AttendanceDate] );
            AttendanceMaster[AttendanceDate] < NextPresence_
        )
    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_ )
)

     

     

     

     

    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

    Cheers 

25 Replies

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        Hi Anonymous 

        Can you explain what the measure is supposed to do and where/how you are going to use it (in a visual, with what fields in the visual, etc.)?

        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

        Cheers 

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    Anonymous 

     

    Try something like this 

     

    Consecutive Absences of 3 or more days = 
IF (
    COUNTX (
        FILTER (
            AttendanceMaster,
            'AttendanceMaster'[ChildID]
                = EARLIER ( 'AttendanceMaster'[ChildID] )
                && 'AttendanceMaster'[AttendanceDate]
                    IN {
                        EARLIER ( AttendanceMaster[AttendanceDate] ),
                        EARLIER ( AttendanceMaster[AttendanceDate] ) + 1,
                        EARLIER ( AttendanceMaster[AttendanceDate] ) - 1
                    }
                && AttendanceMaster[IsPresent]
                    = FALSE ()
        ),
        AttendanceMaster[AttendanceDate]
    ) = 3,
    "Yes",
    "No"
)

     



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      nandukrishnavs ,

       

      Thanks bro. I will try this DAX function. I will let you know if this work well. 

       

      Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      nandukrishnavs ,

       

      Thank you. The DAX is working well. 

       

      Thank you very much