Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm trying to create an Index of times, to show whether individual runners are getting faster or slower over time, with a runner's first time as 100.0 and any decrease in future times being expressed as a reduction, e.g. a 10% faster time would be expressed as 90.0, and so on.
I understand how to achieve this in a couple of ways using Excel, with some combination of MINIFS and pivot tables, but I'd like to do this in Power BI.
I thought it would be quite straightforward to create a column that shows a runners first time and then to use this to further calculate the Index value (time / first time), but I don't seem to be able to achieve this.
Here's an extract of the "data" table where the information is held:
event_name | event_date | event_number | runner_position | runner_name | runner_gender | runner_time |
Lyme Park parkrun | 25/02/2023 | 390 | 1 | Runner a | Male | 00:19:20 |
Lyme Park parkrun | 25/02/2023 | 390 | 2 | Runner b | Male | 00:20:32 |
Lyme Park parkrun | 25/02/2023 | 390 | 3 | Runner c | Male | 00:20:40 |
Lyme Park parkrun | 25/02/2023 | 390 | 4 | Runner d | Male | 00:21:45 |
Lyme Park parkrun | 04/03/2023 | 391 | 1 | Runner e | Male | 00:18:46 |
And here's the custom column I have tried to create, which currently only works when I manually enter a runner's name ("John Smith" in the example below):
In Excel I would simply refer to the specific cell within the current row of the "runner_name" column, but I realise this isn't the right approach in Power BI. I have searched online and tried to use the EARLIER function, but receive error messages which I have been unable to resolve.
Any help with this and/or how to produce the Index I described would be really appreciated!
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
No problem, here's a one drive link to some sample data: example_data.csv
I'd like to create:
Thanks
Hi,
These calculated column formulas should answer the first 2 questions
Earliest date of runner = CALCULATE(MIN(Data[event_date]),FILTER(Data,Data[runner_name]=EARLIER(Data[runner_name])))
Time at earliest date = LOOKUPVALUE(Data[runner_time],Data[event_date],Data[Earliest date of runner],Data[runner_name],Data[runner_name])
Hope this helps.
Thank you for your help and time. I tried to implement this but received the error: "A table of multiple values was supplied where a single value was expected." when I try to create the Time at earliest date column".
Unfortunately I can't see any more detail about which column is generating this error.
You are welcome. Share the download link of the PBI file. Show the problem and expected result clearly.
Thanks very much for sharing that solution. Do you know if it's possible to adapt this approach to allow me to chart runner progress over time, rather than the first vs latest run?
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |