cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
descalabro Frequent Visitor
Frequent Visitor

Dax to compare records in adjacent rows.

Hello,

 

I'm working on a system to report Outlook calendar events, mainly the type of event, the total time spent on each type of event and the total working hours of each team member. I use an add-in to export all the data into an Access file.

 

My problem is with overlapping events. Here is an example:

 

overlapping tasks.png

 

 

What I have in PBI is a query to combine reports from several team-members into a table and sort all events by 'Start'. I also have a calculated column named 'Day' to get only the date from the 'Start' datetime (example in Excel):

 

Excel.png

 

So, I would need something like:

 

 

WorkTime = IF( Event[End] > nextEvent[Start]); [Duration] - ((Event[End] - nextEvent[Start]) / 2); [Duration])

 

 

This is meant to subtract half of the overlapping time to both overlapping events.

And this would have to be filtered by 'Day' and 'EmployeeID'.

 

I am aware this would have to be completed with further conditions to account for other overlapping situations, but at this point I'm concerned with the method to compare adjacent records.

 

Can someone get me started on this?

 

Thank you,

 

 

Tiago Jordão

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Dax to compare records in adjacent rows.

The FILTER expression would look like this (you can only pass one predicate to the function, so you will have to use the && operator)

 

FILTER(Table1 , [EmployeeName]= EARLIER( [EmployeeName] ) && [Day]=EARLIER([Day]))

 

From what I understand, you are primarily interested in getting the value in the [Start] column for the next row.

 

I would write something like this:

 

CALCULATE( MIN(Table1[Start]) 
     , ALLEXCEPT(Table1, Table1[EmployeeName])
     , FILTER( ALL(Table1[Start]), [Start] > EARLIER([Start]) )
)

 

CALCULATE( MIN(Table1[Start]) 
     , FILTER(Table1
, [EmployeeName]= EARLIER( [EmployeeName] )
&& [Day]=EARLIER([Day])
)
, FILTER( ALL(Table1[Start]), [Start] > EARLIER([Start]) )
)

 

Note that EARLIER does not mean what most people think it means when starting with DAX.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Dax to compare records in adjacent rows.

Generally when doing things of this nature, you want to use the EARLIER function.

https://msdn.microsoft.com/en-us/library/ee634551.aspx

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Moderator v-caliao-msft
Moderator

Re: Dax to compare records in adjacent rows.

Hi @descalabro,

 

To get the End time of previous subject, you need to create a rank column by using RANKX function, and then use LOOKUPVALUE function to get that value.

Rank = RANKX(FILTER(Table1,Table1[Day]=EARLIER(Table1[Day])),Table1[Start],,ASC)
PreviousSubjectEnd = LOOKUPVALUE(Table1[End],Table1[Rank],Table1[Rank]-1,Table1[Day],Table1[Day])
Capture.PNGCapture1.PNG

 

Reference
https://msdn.microsoft.com/en-us/library/gg492185.aspx?f=255&MSPPError=-2147217396
https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

Regards,

Charlie Liao

descalabro Frequent Visitor
Frequent Visitor

Re: Dax to compare records in adjacent rows.

Thank you for all your replies.

 

@v-caliao-msft, Thank you. I've successfully applied your method, but I've looked everywhere for a way to add a second criteria so that the ranking can be done by the daily tasks of each team-member. I don't want to rank the times between team-members, I just need to provide an indication so the DAX will not compare between datetimes of diferent people within the same day. I can't seem to find a filtering option that doesn't require some kind of evaluation; something like:

 

 Rank = RANKX(FILTER(Table1, Table1[EmployeeName]= 'EachEmployee'; Table1[Day]=EARLIER(Table1[Day])),Table1[Start],,ASC)

I know the syntax doesn't make sense, it's just an idea of what I want to do.

 

Thanks,

 

Tiago Jordão

Re: Dax to compare records in adjacent rows.

The FILTER expression would look like this (you can only pass one predicate to the function, so you will have to use the && operator)

 

FILTER(Table1 , [EmployeeName]= EARLIER( [EmployeeName] ) && [Day]=EARLIER([Day]))

 

From what I understand, you are primarily interested in getting the value in the [Start] column for the next row.

 

I would write something like this:

 

CALCULATE( MIN(Table1[Start]) 
     , ALLEXCEPT(Table1, Table1[EmployeeName])
     , FILTER( ALL(Table1[Start]), [Start] > EARLIER([Start]) )
)

 

CALCULATE( MIN(Table1[Start]) 
     , FILTER(Table1
, [EmployeeName]= EARLIER( [EmployeeName] )
&& [Day]=EARLIER([Day])
)
, FILTER( ALL(Table1[Start]), [Start] > EARLIER([Start]) )
)

 

Note that EARLIER does not mean what most people think it means when starting with DAX.

View solution in original post

descalabro Frequent Visitor
Frequent Visitor

Re: Dax to compare records in adjacent rows.

@LaurentCouartou, thank you, this is excellent help!

 

Before you edited your answer, I changed it a little bit:

 

NextTaskStart = CALCULATE(MIN('TeamReport'[Start]);
     ALLEXCEPT('TeamReport'; 'TeamReport'[TeamMember]; 'TeamReport'[Day]);
	 FILTER(ALL('TeamReport'[Start]); [Start] > EARLIER([Start]))
)

It seems to be working perfectly (different names here):

 

DAX overlapping tasks.png

Would you recommend doing it as you posted?

 

 

TJ

Highlighted

Re: Dax to compare records in adjacent rows.

I recommend you use the formula that returns the expected results. Smiley Happy

 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 473 members 4,367 guests
Please welcome our newest community members: