Top N filtered by separate Date and Time table

[Anonymous]

Hi all,

I want to have a measure that calculates the busiest hour of a period. This period could be as long as a day, full week, month, quarter or year. So far I have not been able to get a Top N measure including two filters (date and time). 

My datamodel has a separate Date and Time table. So I can't make use of a timestamp.

The Time table is build up as followed:

Time	AM/PM	Hour Label	Hour
00:00	A.M.	00	0
01:00	A.M.	01	1
02:00	A.M.	02	2
03:00	A.M.	03	3

The Date table is build up as followed (just a regular Date table):

Date	Year	Month	Day
04/04/2022	2022	April	04
03/04/2022	2022	April	03
02/04/2022	2022	April	02

My Fact table is build up as followed:

ID	Date	Time	Counts
1	04/04/2022	11:00	212
2	04/04/2022	11:00	643
3	03/04/2022	09:00	342
4	02/04/2022	09:00	231

Here I have the following measure in place: "Total Counts = SUM ( Counts )"

The Fact table is ofcource connected with the Date and Time table by a one to many relationship.

I want to use the default Card visual of Power BI to display the total Counts of the Peak Hour. Next to this I want to use another Card visual to display the Date and Time of when the Peak Hour have been occurred.

Currently I am using the default Top N filter option in the Filter Panel of Power BI to gather the Top 1 by Hour of my regular "Total Counts" measure. This will give me the Peak Hour, but summed up over the whole period. So for example, If we use the counts and dates of the above tables it will display the following: 11:00 AM - 855.

But this is not what I want to display. I want to display the following in my Power BI report: 04/04/2022 - 11:00 AM - 643.

How can I manage to do this with a Measure only? I have experienced with a measure which contains two TOPN functions but this does not work for me. See below:

Top Counts Day and Hour =

CALCULATE (

[Total Counts],

TOPN ( 1, ALL ( 'Date'[Date] ), [Total Counts], DESC ),

TOPN ( 1, ALL ( 'Hour'[Time] ), [Total Counts], DESC )

)

If you need additional information please let me know. 

[johnt75 · Accepted solution]

You could try something like

Peak Hour =
var summaryTable = GENERATE( CROSSJOIN( VALUES('Date'[Date]), VALUES('Time'[Time])),
ROW( "@total count", [Total Count])
)
var top1 = TOPN( 1, summaryTable, [@total count], DESC)
return CONCATENATEX( top1, [Date] & " - " & [Time] & " - " & [@total count], ", ")

This will return multiple strings joined by ", " in the case that more than hour has the same high value.

[Anonymous]

Hi @johnt75,

Thank you very much! This works perfect 😀