The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to 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])
Proud to be a PBI Community Champion
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.
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 |
@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])
Proud to be a PBI Community Champion
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
137 | |
106 | |
103 | |
73 | |
59 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
86 |