Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Extract most frequent value between two times

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,

itemTimestamp    
E36003/11/2019 01:00:30    
E36003/11/2019 01:07:02    
E36003/11/2019 01:08:32    
E36003/11/2019 01:11:02 Start TimeEnd TimeMost Frequent Value
E36003/11/2019 01:18:03 3/11/2019 1:003/11/2019 2:00E360
E36003/11/2019 01:20:53 3/11/2019 2:003/11/2019 3:00A178
E36003/11/2019 01:23:49    
E36003/11/2019 01:26:31    
E36003/11/2019 01:50:55    
E36003/11/2019 01:54:08    
E36003/11/2019 01:56:21    
E36003/11/2019 02:04:08    
E36003/11/2019 02:06:35    
E36003/11/2019 02:09:13    
E36003/11/2019 02:11:53    
E36003/11/2019 02:14:27    
E36003/11/2019 02:17:08    
A17803/11/2019 02:19:49    
A17803/11/2019 02:22:22    
A17803/11/2019 02:33:49    
A17803/11/2019 02:36:14    
A17803/11/2019 02:49:35    
A17803/11/2019 02:52:56    
A17803/11/2019 02:55:16    
A17803/11/2019 02:57:43    
A17803/11/2019 03:00:31    
1 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])

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

3 REPLIES 3
v-xicai
Community Support
Community Support

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.

 

3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Finally, choose the table visual to display the result.

 

4.png 

 

 

 

 

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
Not applicable

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,

 

StartTimeEndTimeMost Probable Value
3/11/2019 1:003/11/2019 2:00E360
3/11/2019 2:003/11/2019 3:00A178
3/11/2019 3:003/11/2019 4:00A178

@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])

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.