cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KDavidP1987 Regular Visitor
Regular Visitor

Linking TimeStamp to Value between Dates in Seperate Table

Hello,

 

I'm new to Power Query and DAX, trying to recreate some reports in it to improve efficiency in their generation.  Having some trouble mimicking a few calculations performed in Excel otherwise.

 

One such calculation I'm struggling to recreate is an INDEX MATCH Array type value, which compares the TIMESTAMP of when agent's perform an action (working a ticket/ Closing a ticket) and the role they were in when that action took place (accounting for job role changes over the years).

 

I have Timestamp based data in various tables, showing when agents worked tickets, closed tickets, etc.  In another separate table I have EmployeeHistory info, which lists out their roles, teams, start date, and end date information.

 

Below is an example of the dataset

 

Table: MASTER3_TIMETRACKING

MrID, mrTIMEDATE, mrTIMEUSER

1234, 05/05/2019, E0001

1244, 05/06/2019, E0023

 

Table: tblEmployeeHistory

EmployeeID, Role, Team, Start Date, End Date

E0001, Tier 1, Help Desk, 01/01/2017, 12/31/2018

E0001, Tier 2, Help Desk, 01/01/2019, 01/01/2099

E0023, Network Engineer, Networking, 01/01/2019, 01/01/2099

 

I'm trying to get a column added to the end of the time tracking table, for later pivoting, which will show the agent's role and team based on the timestamp of action.

 

In excel the array formula looks like this:

={IFERROR(INDEX(EmployeeHistory[Role],MATCH(1,(EmployeeHistory[EmployeeName]=[mrTIMEUSER)*([@Date]>=EmployeeHistory[Start Date])*([@Date]<=EmployeeHistory[End Date]),0)),"Invalid")}

 

I need to recreate this concept in DAX referencing the tables and columns themselves.

 

Is there a way to accomplish this?

 

Sincerely,

Kristopher

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Linking TimeStamp to Value between Dates in Seperate Table

this would work in M

let
    Source = MASTER3_TIMETRACKING,
    #"Merged Queries" = Table.NestedJoin(Source, {"mrTIMEUSER"}, tblEmployeeHistory, {"EmployeeID"}, "Custom", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Custom", {"Role", "Start Date", "End Date"}, {"Role", "Start Date", "End Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Changed Type", each [Start Date] <= [mrTIMEDATE] and [End Date] >= [mrTIMEDATE]),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Start Date", "End Date"})
in
    #"Removed Columns"

idea is following:

 

1) you merge the queries based on EmployeeID (only)
2) you expand the [Role], [Start Date] and [End Date] (at this point you will have more rows than in the beginning)

3) you filter the dates based on the [mrTIMEDATE] (you should have the same number of rows as in 1) now)
4) you remove unnecessary columns

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

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Stachu Super Contributor
Super Contributor

Re: Linking TimeStamp to Value between Dates in Seperate Table

try this code

Column =
VAR __MrTIMEUSER = 'MASTER3_TIMETRACKING'[mrTIMEUSER]
VAR __mrTIMEDATE = 'MASTER3_TIMETRACKING'[mrTIMEDATE]
VAR __EmployeeHistory =
    FILTER (
        'tblEmployeeHistory',
        'tblEmployeeHistory'[EmployeeID] = __MrTIMEUSER
            && 'tblEmployeeHistory'[Start Date] <= __mrTIMEDATE
            && 'tblEmployeeHistory'[End Date] >= __mrTIMEDATE
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'tblEmployeeHistory'[Role], TRUE () ),
        __EmployeeHistory
    )
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

KDavidP1987 Regular Visitor
Regular Visitor

Re: Linking TimeStamp to Value between Dates in Seperate Table

Hey @Stachu ,

 

Apologies for my naivete and inexperience, with regard to Excel Power Query and DAX programming, but I have a quick question regarding the code you shared.

 

Any time I try and generate a custom column which includes a variable declaration, I get the following error...

Token Eof expected.

 

It isn't the first solution I've ran into this with, but I haven't been able to figure out how it should function.  Any guidance in this regard would be most helpful!!

 

For reference:

  1. I go into Power Query table editor
  2. Go to Add Column Tab
  3. Click Custom Column option
  4. Name the column
  5. Paste the following code in
  6. VAR __MrTIMEUSER = 'MASTER3_TIMETRACKING'[mrTIMEUSER]
    VAR __mrTIMEDATE = 'MASTER3_TIMETRACKING'[mrTIMEDATE]
    VAR __EmployeeHistory =
        FILTER (
            'tblEmployeeHistory',
            'tblEmployeeHistory'[EmployeeID] = __MrTIMEUSER
                && 'tblEmployeeHistory'[Start Date] <= __mrTIMEDATE
                && 'tblEmployeeHistory'[End Date] >= __mrTIMEDATE
        )
    RETURN
        CALCULATE (
            FIRSTNONBLANK ( 'tblEmployeeHistory'[Role], TRUE () ),
            __EmployeeHistory
        )
    Receive Eof Error

Sincerely,

Kristopher

 

 

Stachu Super Contributor
Super Contributor

Re: Linking TimeStamp to Value between Dates in Seperate Table

I think there is a bit of confusion here

in Power Query you use M, DAX is used in the model itself (in Excel it was called Power Pivot to differentiate)

 

you asked for DAX solution in your post, the one I provided will work in the model

for M based solution it's a bit different approach, I can followup later this week

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

Proud to be a Datanaut!

KDavidP1987 Regular Visitor
Regular Visitor

Re: Linking TimeStamp to Value between Dates in Seperate Table

Thank you for the great information, @Stachu!  Apologies for my inexperience and lack of knowledge.  Yes, I was confusing the portion relating to PowerQuery with that of Power Pivot, and trying to write DAX in the custom column for M-Code.

 

I suppose that means though that the solution would work if I apply it to the data model in Power Pivot.  I'll have to give that a shot later to find out.

 

Any additional info you would like to provide would be most appreciated, as I am still learning all about Power BI, and need to identify best practices to follow.

Stachu Super Contributor
Super Contributor

Re: Linking TimeStamp to Value between Dates in Seperate Table

this would work in M

let
    Source = MASTER3_TIMETRACKING,
    #"Merged Queries" = Table.NestedJoin(Source, {"mrTIMEUSER"}, tblEmployeeHistory, {"EmployeeID"}, "Custom", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Custom", {"Role", "Start Date", "End Date"}, {"Role", "Start Date", "End Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Changed Type", each [Start Date] <= [mrTIMEDATE] and [End Date] >= [mrTIMEDATE]),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Start Date", "End Date"})
in
    #"Removed Columns"

idea is following:

 

1) you merge the queries based on EmployeeID (only)
2) you expand the [Role], [Start Date] and [End Date] (at this point you will have more rows than in the beginning)

3) you filter the dates based on the [mrTIMEDATE] (you should have the same number of rows as in 1) now)
4) you remove unnecessary columns

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

Proud to be a Datanaut!

View solution in original post

Stachu Super Contributor
Super Contributor

Re: Linking TimeStamp to Value between Dates in Seperate Table

@KDavidP1987 
If the problem is solved, can you mark the relevant post as a solution? That way other users can find answers more easily

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

Proud to be a Datanaut!

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,477)