Group By

[AnonymousUK]

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.

[v-lili6-msft]

hi  @AnonymousUK 

"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

[Fowmy]

@AnonymousUK 

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 🙂

YouTube  LinkedIn
 

[AnonymousUK]

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.

[Fowmy]

@AnonymousUK 

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 🙂

YouTube  LinkedIn

[AnonymousUK]

Apologies for the delay in replying to you. Thank you for your support regarding this. Hopefully I can explain more clearly what I require. Please see the below example data:

Race	Name	Country	Time	Leg
100m	Name 2	A	60.90	1
100m	Name 2	A	60.84	1
100m	Name 2	A	60.55	1
100m	Name 2	A	61.32	1
100m	Name 2	A	60.00	1
100m	Name 2	A	61.00	1
50m	Name 2	A	27.00	1
50m	Name 2	A	27.00	1
100m	Name 2	A	61.00	1
100m	Name 2	A	60.00	1
100m	Name 3	A	62.40	1
50m	Name 7	A	27.84	1
50m	Name 2	A	27.71	2
50m	Name 2	A	27.89	2
50m	Name 2	A	27.62	2
50m	Name 2	A	27.87	2
50m	Name 5	A	30.45	2
100m	Name 6	A	68.45	2
100m	Name 6	A	69.45	2
50m	Name 9	A	32.45	2
50m	Name 1	A	26.45	3
50m	Name 5	A	26.45	3
50m	Name 6	A	32.02	3
100m	Name 8	A	59.45	3
100m	Name 8	A	59.45	3
50m	Name 9	A	32.36	3
50m	Name 1	A	25.77	4
100m	Name 1	A	54.44	4
100m	Name 1	A	54.31	4
100m	Name 1	A	54.77	4
100m	Name 1	A	54.45	4
100m	Name 1	A	53.45	4
100m	Name 1	A	54.45	4
50m	Name 4	A	25.31	4
50m	Name 4	A	25.18	4
50m	Name 4	A	25.26	4
50m	Name 4	A	25.19	4
50m	Name 4	A	25.18	4
100m	Name 4	A	54.65	4
100m	Name 4	A	54.21	4
100m	Name 4	A	53.45	4
50m	Name 4	A	24.45	4
50m	Name 4	A	24.45	4

The outcome I require is the fastest team per country per race using one athlete per leg. There are four legs of the race.

I have used the Advanced Group By function on race, country and leg with the following operation minimum time. I get the following output:

Race	Name	Country	Time	Leg
100m	Name 2	A	60.00	1
100m	Name 6	A	68.45	2
100m	Name 8	A	59.45	3
100m	Name 1	A	53.45	4
50m	Name 2	A	27.00	1
50m	Name 2	A	27.71	2
50m	Name 1	A	26.45	3
50m	Name 4	A	24.45	4

The issue I have is with the 50m race and athlete 'Name 2' for legs 1 and 2 (highlighted above). Only one athlete can perform one leg and I need the fastest time for each leg (as long as the athlete doesn't performed another leg).

I have used the Advanced Group By function using the following variables: Race, Country and Athlete. I used the minimum operation for the variable 'Time' to give me the fastest time for each athlete per race and country. I then did another Advanced Group By function using the following variables: Race, Country and Leg. This gave me the following output:

Race	Name	Country	Time	Leg
100m	Name 2	A	60.00	1
100m	Name 6	A	68.45	2
100m	Name 8	A	59.45	3
100m	Name 1	A	53.45	4
50m	Name 2	A	27.00	1
50m	Name 1	A	26.45	3
50m	Name 4	A	25.18	4

The issue with the above is that there are only three athletes for the race 50m. Using the Advanced Group By function on Race, Country and Athlete resulted in no athletes for leg 2 (these athletes had faster times for other legs).

I require the following output:

Race	Name	Country	Time	Leg
100m	Name 2	A	60.00	1
100m	Name 6	A	68.45	2
100m	Name 8	A	59.45	3
100m	Name 1	A	53.45	4
50m	Name 7	A	27.84	1
50m	Name 2	A	27.62	2
50m	Name 1	A	26.45	3
50m	Name 4	A	24.45	4

The above output is the fastest combination for country A for the 100m and the 50m races. For the 50m race the above output has taken the fastest times for leg 2,3 and 4. For Leg 1 a slower time has been taken because the fastest time was that of the athlete with the fastest time for leg 2. If the fastest time for leg 1 was used and the fastest time of another athlete for leg 2 the combination would have been slower.

How do I obtain the above? Will it require IF statements of some sort?

Thank you.

[Fowmy]

@AnonymousUK 

Name 2 has recorded the fastest time both in Leg 1 and 2 for the same race. To pick the fastest for Leg 1, you drop Name 2 though he has fastest in it because he recorded the best time in leg 2.

What would be the logic to apply here?

________________________

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 🙂

YouTube  LinkedIn

[AnonymousUK]

Apologies, I am not sure I understand your question regarding the logic? 

The logic needs to be that the fastest combination is always selected for each country and race.

The fastest athlete for each leg needs to be selected however, if the athlete performs another leg then the next fastest time for another athlete must be selected. The fastest combination overall must be selected which is why the required output is as follows:

Race	Name	Country	Time	Leg
100m	Name 2	A	60.00	1
100m	Name 6	A	68.45	2
100m	Name 8	A	59.45	3
100m	Name 1	A	53.45	4
50m	Name 7	A	27.84	1
50m	Name 2	A	27.62	2
50m	Name 1	A	26.45	3
50m	Name 4	A	24.45	4

I need athlete 'Name 2' to swim leg 2 rather leg 1 because this means that the overall time for all four athletes for 'Country A' for the 50m race is the fastest (overall time of 01:46.36) . If athlete 'Name 2' had performed leg 1 then I would have the following output which is a slower overall time (overall time of 01:48.40).

Race	Name	Country	Time	Leg
100m	Name 2	A	60.00	1
100m	Name 6	A	68.45	2
100m	Name 8	A	59.45	3
100m	Name 1	A	53.45	4
50m	Name 2	A	27.00	1
50m	Name 5	A	30.5	2
50m	Name 1	A	26.45	3
50m	Name 4	A	24.45	4

Does there need to be an IF statement in the code? How do I code to get the above outcomes?

Thank you.

[amitchandak]

@AnonymousUK , In this case Min(Table[Time]) should have worked with other columns in table or Matrix

[AnonymousUK]

Thank you.

Apologies I am not sure I understand. I am a beginner at Power BI. I am using the transform data window to conduct my queries and the data is in a table.

Will the query Min(Table[Time]) ensure the fastest time per leg and only one athlete per leg?

Thank you.