Forum Discussion
Group By
Good Afternoon,
Please see the below section of my data which has been anonymised:
| Race | Name | Country | Time | Leg |
| 50m | A | A | 25.76 | 4 |
| 50m | A | A | 26.45 | 3 |
| 50m | B | A | 26.99 | 1 |
| 50m | B | A | 27.71 | 2 |
| 50m | B | A | 27.89 | 2 |
| 50m | B | A | 27.62 | 2 |
| 50m | C | A | 27.87 | 2 |
| 50m | C | A | 25.31 | 4 |
| 50m | C | A | 25.18 | 4 |
| 50m | C | A | 25.26 | 4 |
| 50m | C | A | 25.19 | 4 |
| 50m | C | A | 25.18 | 4 |
| 50m | D | A | 24.45 | 4 |
| 50m | D | A | 24.45 | 4 |
| 50m | E | A | 30.44 | 2 |
| 50m | F | A | 26.45 | 3 |
| 50m | G | A | 32.02 | 3 |
| 50m | G | A | 27.83 | 1 |
| 50m | H | A | 32.45 | 2 |
| 50m | H | A | 32.35 | 3 |
I need to group the data by the following columns: Race, Country, Name and Leg. The aggregation needs to show the minimum time for each Leg (1,2,3 and 4).
I have used the Group by function in Power BI however, this leaves me with the following example for one country and one race:
| Race | Name | Country | Time | Leg |
| 50m | B | A | 26.99 | 1 |
| 50m | B | A | 23.62 | 2 |
| 50m | A | A | 26.45 | 3 |
| 50m | D | A | 24.45 | 4 |
As you can see from the above table the same athlete is shown for Leg 1 and 2. The output needs to be the fastest times for each leg but with the athletes only appearing once in the relay team.
How do I group the data ensuring the fastest times are taken but the athletes are not duplicated across the legs?
Thank you.
9 Replies
Anonymous , In this case Min(Table[Time]) should have worked with other columns in table or Matrix
Anonymous
I think you need to summarize excluding the Leg to the min for each player across all legs.
New Table = VAR _T = SUMMARIZECOLUMNS( 'TABLE'[Race], 'TABLE'[Country], 'TABLE'[Name], "_Min", min(Table[Time]) ) RETURN _T________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Thank you.
I have tried this code in the formula box at the top in power query editor and it did not work. I have tried it in the normal window of Power BI and it has returned some data however, it only provides me the fastest time for each athlete not each leg. I was already able to do this using the Group By function. This is not what I need, please see the original post as I need to ensure I have the fastes time per leg without having the same athlete competing on more than one leg.
Also what type of code is this that I am using? Why did it not work in the power query editor?
Thank you.
Anonymous
As per your sample data, Name B has appeared in leg 1 and 2 and each leg has a min of 26.99 and 27.62, so it appears twice. Now, Which line do want to show? 26.99?
Provide expected results based on your sample so I can work it out based on the understanding of the logic.________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
hi Anonymous
"I need to group the data by the following columns: Race, Country, Name and Leg."
for athlete "B", he has two leg, so in this case it should have two row data,
do you mean you just want to show only one row data that is fastest time "23.62" ?
Race Name Country Time Leg 50m B A 26.99 1 50m B A 23.62 2 please share your expected output based on your sample data, that will be a great help.
Regards,
Lin
