Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kadapavel
Helper II
Helper II

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!

 

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
v-huizhn-msft
Employee
Employee

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

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.

 

 Capture.PNG

With regards

Pavel

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

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

TimeWindow.PNG

 

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

Regards
Zubair

Please try my custom visuals

Perfect solution! 

Thank you

Zubair_Muhammad
Community Champion
Community Champion

Hi @kadapavel

 

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

 

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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.