topic TOPN with start end date subcategory table in DAX Commands and Tips

TOPN with start end date subcategory table

Anonymous — Mon, 28 Nov 2022 14:49:22 GMT

Hi,

It's a complex problem (I think) and I'm not even sure how to explain it. But lets try.

I have three tables:

1 - Agents (agents the work on the call center in chat or phone. They can switch between positions over time)

ID_Agent	Name_Agent
1	John
2	Peter
3	Mark
4	Marie

2 - Agent_Funcion

ID_Agent	Function	StartDate	EndDate
1	PHONE	01/10/2022
2	PHONE	01/10/2022	31/10/2022
2	CHAT	01/11/2022
3	CHAT	01/10/2022	31/10/2022
3	PHONE	01/11/2022
4	PHONE	01/10/2022	31/10/2022
4	CHAT	01/11/2022

3 - Agent_Login

Date	ID_Agent	TimeLogin_Seconds
30/10/2022	1	10.800
31/10/2022	1	10.700
01/11/2022	1	8.000
02/11/2022	1	10.000
30/10/2022	2	10.500
31/10/2022	2	10.300
01/11/2022	2	11.000
02/11/2022	2	9.300
30/10/2022	3	10.200
31/10/2022	3	10.200
01/11/2022	3	10.000
02/11/2022	3	10.300
30/10/2022	4	10.800
31/10/2022	4	10.500
01/11/2022	4	8.400
02/11/2022	4	10.100

So here I have that the average of work for each agent is

(This table is just to facilitate the explanation. It is not necessary to be built)

ID_Agent	Month	Average_Seconds
1	october	10.750
1	november	9.000
2	october	10.400
2	november	10.150
3	october	10.200
3	november	10.150
4	october	10.650
4	november	9.250

I have two filters Date and Function and at the end I need to show the TOP1 that has the worst working time average of the function and month selected (in my real data I will show the TOP10). Here are the outcomes expected, based on the example.

If the Filters are OCTOBER + PHONE
Name_Agent Worst_Average_Seconds
Peter 10.400

If the Filters are OCTOBER + CHAT
Name_Agent Worst_Average_Seconds
Mark 10.200

If the Filters are NOVEMBER + PHONE
Name_Agent Worst_Average_Seconds
John 9.000

If the Filters are NOVEMBER + CHAT
Name_Agent Worst_Average_Seconds
Marie 9.250

Thanks in advance for the help.

Best Regards.

Re: TOPN with start end date subcategory table

v-jialluo-msft — Tue, 29 Nov 2022 10:45:52 GMT

^{Hi Anonymous ,}

^{Please follow these steps:}

^{(1) Create new measures}

Avg = AVERAGE(Agent_Login[TimeLogin_Seconds]) MIN = MINX(SUMMARIZE( FILTER( 'Agent_Funcion',[StartDate]<=MAX('Agent_Login'[Date])&&([EndDate]>=MAX('Agent_Login'[Date])||[EndDate]=BLANK())),[ID_Agent],"1",[Avg]),[1])

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Re: TOPN with start end date subcategory table

Anonymous — Wed, 30 Nov 2022 11:28:47 GMT

Hi v-jialluo-msft

Thanks a lot for the answer...

Its almost there. I've forgotten to say that I'm using a calendar table and for this reason I'm struggling with the [EndDate] because I need to use the USERELATIONSHIP or TREATAS to make this relation active, since I have already an active relation with the [StartDate]. Another thing I think is gonna be a problem after this is the TOPN, because I need to make sure that 10 values are going to appear on the table (1 in this example). The MINX will show only the TOP 1, but my real problem is with TOP 10.

I'll keep trying here, and will come back with the answer if I get it.

Thanks a lot again.

Raoni