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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

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 @Anonymous,

 

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 ;).
xOIEmaj

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.
image
fabric-SUbadge
Proud to be a Super User!
Fowmy
Super User
Super User

@Anonymous 

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.