Extract most frequent value between two times

[Anonymous]

Hi,

I am new to power Bi and need some help to extract the most frequent value between a time period. Below is the table where you can see the most frequent value between 1 pm and 2 pm time period is E360 and most frequent value between 2pma and 3 pm is A178. Please help me to do this in Power BI.

Thanks,

item	Timestamp
E360	03/11/2019 01:00:30
E360	03/11/2019 01:07:02
E360	03/11/2019 01:08:32
E360	03/11/2019 01:11:02		Start Time	End Time	Most Frequent Value
E360	03/11/2019 01:18:03		3/11/2019 1:00	3/11/2019 2:00	E360
E360	03/11/2019 01:20:53		3/11/2019 2:00	3/11/2019 3:00	A178
E360	03/11/2019 01:23:49
E360	03/11/2019 01:26:31
E360	03/11/2019 01:50:55
E360	03/11/2019 01:54:08
E360	03/11/2019 01:56:21
E360	03/11/2019 02:04:08
E360	03/11/2019 02:06:35
E360	03/11/2019 02:09:13
E360	03/11/2019 02:11:53
E360	03/11/2019 02:14:27
E360	03/11/2019 02:17:08
A178	03/11/2019 02:19:49
A178	03/11/2019 02:22:22
A178	03/11/2019 02:33:49
A178	03/11/2019 02:36:14
A178	03/11/2019 02:49:35
A178	03/11/2019 02:52:56
A178	03/11/2019 02:55:16
A178	03/11/2019 02:57:43
A178	03/11/2019 03:00:31

[v-xicai]

Hi ashmj12,

According to your description, I create sample data to reproduce the scenario. You can implement your demand following steps below.

Firstly, Create new table Calendar and then create column End Time.

Calendar = SELECTCOLUMNS( CROSSJOIN( CALENDAR(DATE(2019,3,1), DATE(2019,3,31)),GENERATESERIES( 0,TIME(23,0,0), TIME(1,0,0) ) ), "dateTime", [Date]& " " &[Value] )

End Time = 'Calendar'[dateTime]+TIME(1,0,0)

Secondly, create column Start Time in original table containing item ,assuming it is named Table1, and then create measure Most Frequent Value.

Start Time = DATE( YEAR(Table1[Timestamp]), MONTH(Table1[Timestamp]),DAY(Table1[Timestamp]) )& " " & TIME(HOUR(Table1[Timestamp]), 0, 0)

Most Frequent Value = MINX ( TOPN ( 1,ADDCOLUMNS ( VALUES ( Table1[item] ),"Frequency",CALCULATE ( COUNT (Table1[item]) ) ), [Frequency], 0 ), Table1[item] )

Thirdly, create relationship between the new table Calendar and table1.

Finally, choose the table visual to display the result.

 

Here is my test pbix file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfdMxuZ5f4NJrhab6v...

Best Regards,

Amy

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

[Anonymous]

I have created a separate table with StartTime and EndTime and would like to have a calculated column with the most probable value within that time period. Can you help me with the formula for a calculated column that returns the most probable value by filtering the raw data between the StartTime and EndTime?

Thanks,

StartTime	EndTime	Most Probable Value
3/11/2019 1:00	3/11/2019 2:00	E360
3/11/2019 2:00	3/11/2019 3:00	A178
3/11/2019 3:00	3/11/2019 4:00	A178

[PattemManohar · Accepted solution]

@Anonymous  Please try this as a New Column in your second table (output table)

MostProbableValue = 
VAR _Table = SUMMARIZE(FILTER(Test295MaxOccurs,Test295MaxOccurs[Timestamp]>=Test295Out[StartTime] && Test295MaxOccurs[Timestamp]<=Test295Out[EndTime]),Test295MaxOccurs[Item],"Count",COUNTROWS(Test295MaxOccurs))
VAR _Index = ADDCOLUMNS(_Table,"Rank",RANKX(_Table,[Count],,DESC))
RETURN SELECTCOLUMNS(FILTER(_Index,[Rank]=1),"Item",[Item])