Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |