Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I would like to allow my users to calculate the average duration between two instances of their choosing. I tried researching a solution, but had little luck. Below is a link to a sample .pbix file I was experimenting with.
I duplicated the table below and assigned different slicers to each. Each table gets a "Start/Finish" slicer and a "Task" slicer. This allows the user to select the instances they want to use for calculation. The desired outcome would be to calculate the difference between the filtered instances for each job and return the average.
For exampple: the user may want to know the average duration between the end of task 1 and the start of task 3.
-one table would be filtered to return the end of task 1
-the other table would be filtered to return the start of task 3
-I need a measure to calculate the duration between these two intances for each job, then return the average of those durations
The data:
Job Task Start/Finish Date
aaa | 1 | start | Monday, January 20, 2020 |
aaa | 1 | finish | Saturday, January 25, 2020 |
aaa | 2 | start | Monday, February 10, 2020 |
aaa | 2 | finish | Saturday, February 15, 2020 |
aaa | 3 | start | Thursday, February 20, 2020 |
aaa | 3 | finish | Tuesday, February 25, 2020 |
bbb | 1 | start | Wednesday, January 15, 2020 |
bbb | 1 | finish | Saturday, January 25, 2020 |
bbb | 2 | start | Saturday, February 15, 2020 |
bbb | 2 | finish | Thursday, February 20, 2020 |
bbb | 3 | start | Friday, February 21, 2020 |
bbb | 3 | finish | Sunday, March 1, 2020 |
ccc | 1 | start | Saturday, February 15, 2020 |
ccc | 1 | finish | Sunday, March 1, 2020 |
ccc | 2 | start | Thursday, March 5, 2020 |
ccc | 3 | finish | Tuesday, March 10, 2020 |
ccc | 3 | start | Sunday, March 15, 2020 |
ccc | 3 | finish | Wednesday, March 25, 2020 |
ddd | 1 | start | Saturday, February 15, 2020 |
ddd | 1 | finish | Monday, February 17, 2020 |
ddd | 2 | start | Thursday, February 20, 2020 |
ddd | 2 | finish | Tuesday, February 25, 2020 |
ddd | 3 | start | Sunday, March 1, 2020 |
ddd | 3 | finish | Wednesday, March 4, 2020 |
Please explain what to do in this scenario:
Couple of questions:
How would you prevent a user from selecting nonsensical items like this:
How are you planning to compute the average in your earlier example?
Do you want a cartesian product, comparing each row in the left table to each row in the right table? That would be the average of 12 comparisons ? I don't think that makes much sense.
Nonsensical inputs should either result in 0 (if they select the same filters for both tables) or a negative (if they select to filter a later instance before an earlier one). One work around could be to return the absolute value, but I think the negative will help the users understand that their inputs are backwards.
The calculation should be the difference between the values returned for that job, then averaged.
I believe I need a DAX formula to link the two tables together via the job column, generates a column containing the duration calculations, and then returns the average of that column.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
18 | |
17 |
User | Count |
---|---|
37 | |
22 | |
19 | |
18 | |
13 |