Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Thank you in advance for your time and help.
Cheers, Kristaps
Solved! Go to Solution.
Do a Group By on the timestamp column with a new column that keeps all rows for each separate time stamp.
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.
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 ;). |
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. | Proud to be a 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 ()
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Do a Group By on the timestamp column with a new column that keeps all rows for each separate time stamp.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |