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...
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 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I have attached the excel file containing excel functions in columns H and I. I ask for your help to change that function to DAX function. I will add two new columns to Power BI.

  • The conversion result of the excel function in Column H will be used as a start date when the child is not present in class
  • The conversion result of the excel function in column I is end date when child absent and just an evaluation of how many days the child is absent.

I try to see consecutive days in a more systematic way. But the disadvantage of the excel function is working days in a year. Can you help me with this too by including holidays like Sundays in the calculation. After that I will make count of start absent days to get the total children who has absent 3 or more consecutive days in a year. 

 

Thank you

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Anonymous 

    So you do not need a measure but calculated columns, it seems. In any case, please explain the ultimate result you want out of this, instead of talking about translating formulae from Excel. The approach in DAX will probably be different. Do you want to identify the children that have been abstent at least three days in a row? Do you want to count them? What do you need exactly???

    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 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes,  I want to count the children that have been abstent at least three days in a row. But in other case I need to identify the absent start date and absent end date for each children by adding 2 colomn on BI. How about your opinion? 

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        Anonymous 

        This calculate column will give you the date on which the absence starts (will return only non-blank on the rows where an absence starts):

        First 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; AttendanceMaster[AttendanceDate] )
)

        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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks bro. 

     

    This help me a little. Thank you.

    I am very happy if you help me on this and I will learn what you have done to this. Please