cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kj89
New Member

Filter out top 3 rows based on same timestamp

Hello,

 

Im new to power bi, and would be very grateful if someone could help me out. 

I have table with 3 columns timestamp, procedure, average_cpu.

kj89_2-1635361645634.png

Everytime when data gets generated its marked with timestamp. Rows are already sorted by average_cpu, but in the result i would like to get new table with only first 3 rows in each timestamp

kj89_0-1635363466071.png

 

Thank you in advance for your time and help.

 

Cheers, Kristaps

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Do a Group By on the timestamp column with a new column that keeps all rows for each separate time stamp.

 

AlexisOlson_0-1635363816374.png

This will generate a step with code that looks like this:

 

= Table.Group(#"Changed Type", {"timestamp"}, {{"Top3", each _, type table}})

 

We want to modify this slightly using Table.MaxN:

 

= Table.Group(#"Changed Type", {"timestamp"}, {{"Top3", each Table.MaxN(_, "avergae_cpu", 3), type table}})

 

 

All that's left is to expand the new Top3 table column by clicking the expand button in the top right corner and pick which columns you want.

 

View solution in original post

3 REPLIES 3
KNP
Super User
Super User

Hi @kj89,

 

This is kind of the same solution as @AlexisOlson has offered, I'm only posting this as I'd already started working on it.

Attached PBIX.

Essentially, adding an index to the grouped table and keeping values where index is less than 3.

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Fowmy
Super User
Super User

@kj89 

Create a new table with the following code:

FILTER(
    ADDCOLUMNS (
        Table1,
        "Top3",
            TABLE1[avergae, cpu]
                IN SELECTCOLUMNS (
                    TOPN (
                        3,
                        ALLEXCEPT ( Table1, table1[timestamp] ),
                        CALCULATE ( MAX ( table1[avergae, cpu] ) )
                    ),
                    Table1[avergae, cpu]
                )
    ),
    [Top3] = TRUE ()
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

AlexisOlson
Super User
Super User

Do a Group By on the timestamp column with a new column that keeps all rows for each separate time stamp.

 

AlexisOlson_0-1635363816374.png

This will generate a step with code that looks like this:

 

= Table.Group(#"Changed Type", {"timestamp"}, {{"Top3", each _, type table}})

 

We want to modify this slightly using Table.MaxN:

 

= Table.Group(#"Changed Type", {"timestamp"}, {{"Top3", each Table.MaxN(_, "avergae_cpu", 3), type table}})

 

 

All that's left is to expand the new Top3 table column by clicking the expand button in the top right corner and pick which columns you want.

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors