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

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