topic Re: Convert excel function to DAX in DAX Commands and Tips

Convert excel function to DAX

Anonymous — Sun, 24 May 2020 13:44:54 GMT

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

Re: Convert excel function to DAX

nandukrishnavs — Sun, 24 May 2020 13:59:12 GMT

Anonymous Please share your sample table and expected output

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 14:52:33 GMT

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

Re: Convert excel function to DAX

AlB — Sun, 24 May 2020 15:08:48 GMT

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

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 15:32:22 GMT

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

Re: Convert excel function to DAX

Greg_Deckler — Sun, 24 May 2020 15:37:15 GMT

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

Re: Convert excel function to DAX

AlB — Sun, 24 May 2020 15:51:47 GMT

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

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 15:53:16 GMT

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

Re: Convert excel function to DAX

nandukrishnavs — Sun, 24 May 2020 16:33:56 GMT

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 🙂

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 16:03:12 GMT

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?

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 16:05:55 GMT

nandukrishnavs ,

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

Thanks

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 16:38:57 GMT

nandukrishnavs ,

Thank you. The DAX is working well.

Thank you very much

Re: Convert excel function to DAX

AlB — Sun, 24 May 2020 16:50:42 GMT

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

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 16:56:41 GMT

AlB ,

Thank you. I will check it and let you know if DAX work well.

Please help me again for the end date of absent if you want.

I am sorry.

Thank you

Re: Convert excel function to DAX

AlB — Sun, 24 May 2020 17:17:30 GMT

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

Re: Convert excel function to DAX

nandukrishnavs — Sun, 24 May 2020 17:08:14 GMT

Anonymous

Start date of the student who had absent 3 or more days in a month.

start Date = VAR countVal = COUNTX ( FILTER ( AttendanceMaster, 'AttendanceMaster'[ChildID] = EARLIER ( 'AttendanceMaster'[ChildID] ) && MONTH ( 'AttendanceMaster'[AttendanceDate] ) = MONTH ( EARLIER ( AttendanceMaster[AttendanceDate] ) ) && AttendanceMaster[IsPresent] = FALSE () ), AttendanceMaster[AttendanceDate] ) VAR result = IF ( countVal >= 3, MINX ( FILTER ( AttendanceMaster, AttendanceMaster[ChildID] = EARLIER ( AttendanceMaster[ChildID] ) && MONTH ( AttendanceMaster[AttendanceDate] ) = MONTH ( EARLIER ( AttendanceMaster[AttendanceDate] ) ) && AttendanceMaster[IsPresent] = FALSE () ), AttendanceMaster[AttendanceDate] ), BLANK () ) RETURN result

End date of the student who had absent 3 or more days in a month.

end Date = VAR countVal = COUNTX ( FILTER ( AttendanceMaster, 'AttendanceMaster'[ChildID] = EARLIER ( 'AttendanceMaster'[ChildID] ) && MONTH ( 'AttendanceMaster'[AttendanceDate] ) = MONTH ( EARLIER ( AttendanceMaster[AttendanceDate] ) ) && AttendanceMaster[IsPresent] = FALSE () ), AttendanceMaster[AttendanceDate] ) VAR result = IF ( countVal >= 3, MAXX( FILTER ( AttendanceMaster, AttendanceMaster[ChildID] = EARLIER ( AttendanceMaster[ChildID] ) && MONTH ( AttendanceMaster[AttendanceDate] ) = MONTH ( EARLIER ( AttendanceMaster[AttendanceDate] ) ) && AttendanceMaster[IsPresent] = FALSE () ), AttendanceMaster[AttendanceDate] ), BLANK () ) RETURN result

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

Here is my latest blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/1097786

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 17:28:43 GMT

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?

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 17:30:09 GMT

nandukrishnavs ,

I will try this. Thank you. I will let you know if this DAX work well

Re: Convert excel function to DAX

Anonymous — Sun, 24 May 2020 17:55:54 GMT

AlB , Pleaee attach

Re: Convert excel function to DAX

AlB — Sun, 24 May 2020 17:56:31 GMT

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