Creating a column using different clauses and like statements

[HarryT]

Hi everyone,

We are in the process of moving some dashboards from QlikView to PBI.  Right now we are trying to recreate this field / column in PBI.

Here is the load statement in Qlik:

first_res_date = (select min(entry_date)

     from "whd_prod".dbo."HISTORY_ENTRY" he

        where he.JOB_TICKET_ID = jt.JOB_TICKET_ID

        and (he.ENTRY_TEXT like '%to Resolved'

         or  he.ENTRY_TEXT like '%to Closed'))

What this is is creating a first resolution date.  Whether is it put in resolved or closed first.  Using this field helps with math later on in determining timing to resolved/closed from reported dated.

So far I have this in PBI:

Column = MIN(HISTORY_ENTRY[ENTRY_DATE].[Date])

I just don't understand the 'where" and 'and' and 'or' statements.  They don't pop up when entering them.

Any assistance or point me in the right direction is much appreciated.

Thanks!

[Anonymous]

can you load some sample data and what the goal output would be?

[HarryT]

Hi Nick,

Here is some sample data from the HISTORY_ENTRY table.

JOB_TICKET_ID	ENTRY_DATE	ENTRY_TEXT
227925	5/30/2019 9:40	Status changed from Open to Closed
227925	5/30/2019 9:40	Added new tech note.
227925	5/30/2019 9:31	Reassigned-Ticket E-Mail sent to Joe Smith
227925	5/30/2019 8:03	Request Type changed from Business Systems
227925	5/30/2019 8:03	Status changed from Open to In Progress
227925	5/29/2019 18:19	Created by Molly Davis
227925	5/29/2019 18:19	Assigned to Joe Smith
227922	5/27/2019 8:03	Status changed from Open to Closed
227922	5/22/2019 8:03	Status changed from Open to Resolved
227922	5/21/2019 18:19	Created by Molly Davis
227922	5/21/2019 18:19	Assigned to Joe Smith

So the idea here would be getting a new field called something like "First_Resolution_Date"

This would be the date the first time you see either "To Resolved" or "To Closed" in the history entry notes.

In this example:

the First_Resolution_Date for Job_Ticket_ID 227925 would be 5/30/2019 9:40:14 AM

the First_Resolution_Date for Job_Ticket_ID 227922 would be 5/22/2019 8:03:25 AM (note this one has both a "To Resolved" and a "To Closed", but the "To Resolved" is earlier.

Here is how this table should look:

JOB_TICKET_ID	ENTRY_DATE	First_Resolution_Date	ENTRY_TEXT
227925	5/30/2019 9:40	5/30/2019 9:40:14 AM	Status changed from Open to Closed
227925	5/30/2019 9:40	5/30/2019 9:40:14 AM	Added new tech note.
227925	5/30/2019 9:31	5/30/2019 9:40:14 AM	Reassigned-Ticket E-Mail sent to Joe Smith
227925	5/30/2019 8:03	5/30/2019 9:40:14 AM	Request Type changed from Business Systems
227925	5/30/2019 8:03	5/30/2019 9:40:14 AM	Status changed from Open to In Progress
227925	5/29/2019 18:19	5/30/2019 9:40:14 AM	Created by Molly Davis
227925	5/29/2019 18:19	5/30/2019 9:40:14 AM	Assigned to Joe Smith
227922	5/27/2019 8:03	5/22/2019 8:03:25 AM	Status changed from Open to Closed
227922	5/22/2019 8:03	5/22/2019 8:03:25 AM	Status changed from Open to Resolved
227922	5/21/2019 18:19	5/22/2019 8:03:25 AM	Created by Molly Davis
227922	5/21/2019 18:19	5/22/2019 8:03:25 AM	Assigned to Joe Smith

There is also a master table called "JOB_TICKET" that is linked on the JOB_TICKET_ID and that is why in the orginal query we had:

where he.JOB_TICKET_ID = jt.JOB_TICKET_ID

Maybe it isn't needed in Power BI.

I hope this helps.

Thanks again!

[Anonymous · Accepted solution]

@HarryT 

I went ahead and did this all in Power Query, so I attached the pbix file below so you walk through each step and see what I did. Too much to explain all here as it's much easier to just see it, but let me know if you have any questions. One thing, I did split EntryDate column into two columns, one for date and one for time. The way compression works you generally dont want those values combined.

Final table

Here's the file

https://1drv.ms/u/s!Amqd8ArUSwDS1TmH8k5RklCseY-E?e=mJ9A6A