Forum Discussion

kadapavel's avatar
kadapavel
Icon for Helper II rankHelper II
9 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.01

   20%          00.00.02   

   12%          00.00.03

I would create a new table where row one would have 

   Value     Time

    20%       00.00.03

Thanks in advance!

 

 

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

8 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Hi kadapavel

     

    Go to Modelling Tab.... select the NEW TABLE button

     

    NewTable =
ROW ( "Value", MAX ( TableName[Value] ), "Time", MAX ( TableName[Time] ) )
  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 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
      9 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
        9 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