Microsoft SQL DirectQuery Pull Only Latest Date from Table

[mattyj2009]

Good Morning,

I have a SQL table that is added to every minute.  However I only want to pull the latest status from the SQL database.  We are doing direct query as it is very important that we are able to press the refresh button in Power BI service and get the most up-to-date information.  

Here is a sample of the SQL data we're pulling. 

id	FullName	CallTransferStatusDesc	TotalSecondsAvailable	TotalSecondsNotSet	TotalSecondsOnCall	TotalSecondsWrappingUp	TotalSecondsOnBreak	TotalSecondsDialingOut	TotalSecondsBusy	TeamName	CurrentDateTime
4392	Lynnette Landon	On Call	2580	91	646	1434	0	695	4851	Team 1	7/16/19 10:54 AM
4393	Denese Ravencraft	Available	0	0	0	0	0	0	0	Team 1	7/16/19 10:54 AM
4394	Magan Bednar	Available	3668	53	472	1941	789	1070	2051	Team 1	7/16/19 10:54 AM
4395	Alberto Chitty	Available	5791	186	375	274	746	0	1848	Team 2	7/16/19 10:54 AM
4396	Belinda Batson	Available	3541	306	810	639	0	388	1778	Team 2	7/16/19 10:54 AM
4397	Vern Siddiqui	Available	5620	170	428	197	1060	79	2416	Team 2	7/16/19 10:54 AM
4398	Stephen Nakken	Other - Payment	1552	390	199	184	0	0	0	Team 3	7/16/19 10:54 AM
4399	Oswaldo Edelman	Available	53999	0	0	0	0	0	0	Team 3	7/16/19 10:54 AM
4400	Joel Fausto	Available	6020	113	2618	976	0	367	691	Team 3	7/16/19 10:54 AM
4401	Exie Scroggs	Available	0	0	0	0	0	0	0	Team 1	7/16/19 10:54 AM
4402	Shira Whittier	Other - Special	6980	0	0	0	0	0	0	Team 1	7/16/19 10:54 AM
4403	Eldon Mcentire	Dial Out - Ringing Destination	3426	303	2994	990	0	387	2324	Team 1	7/16/19 10:54 AM
4404	Angelique Lanham	Available	1791	5	406	292	0	85	5838	Team 2	7/16/19 10:54 AM
4405	Vivan Castrejon	Other - Walk-in	3068	303	876	587	0	51	733	Team 2	7/16/19 10:54 AM
4406	Herminia Hemond	Other - Walk-in	2503	604	65	91	0	0	0	Team 2	7/16/19 10:54 AM
4407	Jacalyn Yokoyama	Not Set	1290	635	187	182	0	0	596	Team 3	7/16/19 10:54 AM
4408	Phylis Tolbert	Available	2572	142	712	721	878	956	4363	Team 3	7/16/19 10:54 AM
4409	Kendall Margolis	Available	0	0	0	0	0	0	0	Team 3	7/16/19 10:54 AM
4410	Rene Cryer	Dial Out - Ringing Destination	3880	519	1506	864	0	416	4264	Team 1	7/16/19 10:54 AM
4411	Yuette Koepke	Available	0	0	0	0	0	0	0	Team 1	7/16/19 10:54 AM
4412	Vanna Scala	Available	1509	153	539	640	0	141	516	Team 1	7/16/19 10:54 AM
4413	Lynnette Landon	On Call	2580	91	646	1434	0	695	4851	Team 1	7/16/19 10:55 AM
4414	Denese Ravencraft	Available	0	0	0	0	0	0	0	Team 1	7/16/19 10:55 AM
4415	Magan Bednar	Available	3668	53	472	1941	789	1070	2051	Team 1	7/16/19 10:55 AM
4416	Alberto Chitty	Available	5791	186	375	274	746	0	1848	Team 2	7/16/19 10:55 AM
4417	Belinda Batson	Available	3541	306	810	639	0	388	1778	Team 2	7/16/19 10:55 AM
4418	Vern Siddiqui	Other - Special	5739	170	428	197	1060	79	2416	Team 2	7/16/19 10:55 AM
4419	Stephen Nakken	Other - Payment	1552	390	199	184	0	0	0	Team 3	7/16/19 10:55 AM
4420	Oswaldo Edelman	Available	53999	0	0	0	0	0	0	Team 3	7/16/19 10:55 AM
4421	Joel Fausto	Available	6020	113	2618	976	0	367	691	Team 3	7/16/19 10:55 AM
4422	Exie Scroggs	Available	0	0	0	0	0	0	0	Team 1	7/16/19 10:55 AM
4423	Shira Whittier	Other - Special	6980	0	0	0	0	0	0	Team 1	7/16/19 10:55 AM
4424	Eldon Mcentire	Dial Out - Connected	3426	303	2994	990	0	387	2324	Team 1	7/16/19 10:55 AM
4425	Angelique Lanham	Available	1791	5	406	292	0	85	5838	Team 2	7/16/19 10:55 AM
4426	Vivan Castrejon	Other - Walk-in	3068	303	876	587	0	51	733	Team 2	7/16/19 10:55 AM
4427	Herminia Hemond	Other - Walk-in	2503	604	65	91	0	0	0	Team 2	7/16/19 10:55 AM
4428	Jacalyn Yokoyama	Available	1290	937	187	182	0	0	596	Team 3	7/16/19 10:55 AM
4429	Phylis Tolbert	Dial Out - Connected	2614	142	712	721	878	956	4363	Team 3	7/16/19 10:55 AM
4430	Kendall Margolis	Available	0	0	0	0	0	0	0	Team 3	7/16/19 10:55 AM
4431	Rene Cryer	Dial Out - Ringing Destination	3880	519	1506	864	0	416	4264	Team 1	7/16/19 10:55 AM
4432	Yuette Koepke	Available	0	0	0	0	0	0	0	Team 1	7/16/19 10:55 AM
4433	Vanna Scala	Available	1509	153	539	640	0	141	516	Team 1	7/16/19 10:55 AM

From the table above I would only care about the status from 7/16/19 10:55AM for each of the agents.

I haven't been able to figure out a way to filter out all other rows to get the information I need.  I'm planning on having a table that would show the total number of agents per status and would like to have a card or two that has stuff like average of total seconds available and average of total second busy.

Any information to set me on the right track would be appreciated.  Have a great day!!!

[v-lili6-msft · Accepted solution]

hi, @mattyj2009 

You could try these two ways as below:

1. You could use SQL Queries to Retrieve SQL Server Data, then add a conditional in it

https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-sql-server-data-in-po...

2.  In Edit Queries, add a filter step for this query

best regards,

Lin

[mattyj2009]

When I attempt to do option 2 I get the a message saying "This table is empty". 

But I did some tinkering around and I added a new step to set the CurrentDateTime to Date/Time type then did the is latest filter and it worked. 

Just thought I would throw that out just in case someone has a similar experience.