Forum Discussion

kadapavel's avatar
kadapavel
Icon for Helper II rankHelper II
8 years ago
Solved

Max value of time window

Hi Can someone support, I would like to filter data so that use only max value over selected time window and save the value in to new table Example:  Value            Time    10%          00.00....
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    kadapavel

     

    Here are the steps to get every 3rd Second and related Maximum Value for a 3 second period

     

    First I added 2 calculated Columns to filter out every 3rd second

     

    Second =
SECOND ( Table1[SignalTimeStamp] )
    3rd_Second =
IF (
    ( Table1[Second] / 3 )
        - INT ( Table1[Second] / 3 )
        = 0,
    "3rd Second",
    "Others"
)

     

    Final step is to get the Maximum Value for the 3 seconds interval. I used following MEASURE to do this

     

    Max_Value =
CALCULATE (
    MAX ( Table1[Value] ),
    TOPN (
        3,
        FILTER (
            ALL ( Table1 ),
            Table1[SignalTimeStamp] <= SELECTEDVALUE ( Table1[SignalTimeStamp] )
        ),
        Table1[SignalTimeStamp], DESC
    )
)
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi kadapavel,

I am a little confused about your requirement of "selected time window"? You want to create the new table based on a slicer or filter, and the new table change autotally? If it is, we can't achieve it in Power BI, please review this knowledage base. While you can create a table visual and measure, let the slicer/filter affect it. Please share more details for further analysis.

Best Regards,
Angelia

  • kadapavel's avatar
    kadapavel
    Icon for Helper II rankHelper II
    8 years ago

    Hello

    In the example I had only 3 values, in fact my table contains about 2m rows. I’m trying to reduce amount of points, or filter values with selected time window(3s filter, 6s filter or other). I want to be able to pick up max or min value from chosen time window.

    Why?

    Idea is to generate extra table with limited amount of points. This way I try to acheave faster response of visuals without frequent data processing(when time by slicer), another important reason is to be able to use customised visuals where amount of points is limited by 30 000.

     

     

    With regards

    Pavel

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi kadapavel,

      You put the column [SignalTimeStamp] in slicer? If it is, you want to get the max value when you select some time in slicer, please create a measure using the formua, and display it in table visual.

      max=CALCULATE(MAX(Table[Value]),ALLSELECTED(Table))


      Best Regards,
      Angelia

      • kadapavel's avatar
        kadapavel
        Icon for Helper II rankHelper II
        8 years ago

        Hi here is illustration of what I'm trying to reach