Max value of time window

[kadapavel]

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!

[v-huizhn-msft]

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]

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]

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]

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

[Zubair_Muhammad]

Hi @kadapavel

Please see attached file here.

[Zubair_Muhammad · Accepted solution]

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

[kadapavel]

Perfect solution! 

Thank you

[Zubair_Muhammad]

Hi @kadapavel

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

NewTable =
ROW ( "Value", MAX ( TableName[Value] ), "Time", MAX ( TableName[Time] ) )