Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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    
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

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

3 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    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/EfdMxuZ5f4NJrhab6v1HqEoB00xhWXQn__mbqfnPkCsqwg?e=qO6zNB

     

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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
      • PattemManohar's avatar
        PattemManohar
        Icon for Community Champion rankCommunity Champion
        7 years ago

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