Convert excel function to DAX

[Anonymous]

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

[nandukrishnavs]

@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]

@nandukrishnavs ,

Thank you. The DAX is working well. 

Thank you very much

[Anonymous]

@nandukrishnavs ,

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

Thanks

[nandukrishnavs]

@Anonymous  Please share your sample table and expected output

[Anonymous]

Hi my friend,

This is the link of sampel data on excel file:

Sample Data on Excel 

This is the link of Pbix file:

Sample Data Power BI 

Thank you for your help

[AlB]

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 

[Anonymous]

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

[Greg_Deckler]

@Anonymous - See if this assists: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

[Anonymous]

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

[AlB]

@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]

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]

@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 

[AlB · Accepted solution]

@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]

@AlB , Pleaee attach 

[Anonymous]

@AlB ,

I am sorry, 

Your DAX not work on my BI because "A table of multiple values was supplied where a singel value was expected".

How to fix this? 

[AlB]

@Anonymous 

Are you running it on the same data you shared? It's working fine on my side

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]

I have running it on my true data. Do you want to see my data? So that you can know well

[AlB]

@Anonymous 

Yes, please share it. I think I have an idea of what the issue might be (edge cases, of course) but I need the real data to check it out.

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]

@AlB ,

This is link of MyData. 

Please look on it. 

My Data 

Thank you

[AlB]

@Anonymous 

Ok, I had a look. The code is doing what it should. The problem is that there are instances where your data has, for the same day and child, two (or more) rows and with values both TRUE and FALSE in the column isPresent.

One example of this are rows with AttendanceID 774 and 779, for childID 43 on 07/03/2018.  

So that's actually a problem with the data, not with the code. I guess it doesn't make sense to have to rows with inconsistent attendance info for the same date and child, does it? In fact I don't think you ought to have more than one a row for the same date/child at all (even if the info is the same in all those rows).

It is the DISTINCT that causes the error (see in red below). But again, the problem is the data. Why do you have cases like that? It would be a matter of deciding how you want to treat those IF(ISBLANK(previousDate_),TRUE(),CALCULATE(DISTINCT(AttendanceMaster[IsPresent]),ALLEXCEPT(AttendanceMaster,AttendanceMaster[ChildID]),AttendanceMaster[AttendanceDate]=previousDate_))

In fact, there are more oddities with the data. An extreme case is childID 2966 on date 10/08/2019. The info for that date and child is repeated in 15 rows!! 🤔

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