Forum Discussion

chadcook3's avatar
chadcook3
Frequent Visitor
8 years ago

Advanced Condition Column lookup between datetimes

Hi All I have 2 data sets that i want to combine as one. The first is a call data base where i have start and end time of the call The second is the time a ticket was submitted I want to capture the ticket number of each call

**Example Calls Data** Call Start Call End Username

    21/08/2018 9:40 21/08/2018 9:52 Bob
    21/08/2018 9:30 21/08/2018 9:42 Jane

**Example Tickets Data** Ticket Number Submited Username

    123456 21/08/2018 9:41 Bob.Smith42
    123457 21/08/2018 9:41 Jane.Bloggs17

The Third issue is the usernames don't match but i have many solutions for that. **Ideal Result** Call Start Call End Username Ticket Number

    21/08/2018 9:40 21/08/2018 9:52 Bob 123456
    21/08/2018 9:30 21/08/2018 9:42 Jane 123457

I can do this with Excel macro's or SQL, then load the dataset but i'd love to be able to do it directly through Power BI. Any help would be appreciated Thanks

2 Replies

  • Hi,

     

    If the Name can be made the same between both datasets, then we can solve this problem using DAX.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    Hi chadcook3,

     

    You can split the column "Username" of "Example Tickets Data" in the Query Editor with the built-in function. Then it's easy to get "Ticket Number". The concern could be the duplicate usernames. Are there any other conditions we can apply?

     

    Best Regards,

    Dale