Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Measure based on different filters

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

aaa1startMonday, January 20, 2020
aaa1finishSaturday, January 25, 2020
aaa2startMonday, February 10, 2020
aaa2finishSaturday, February 15, 2020
aaa3startThursday, February 20, 2020
aaa3finishTuesday, February 25, 2020
bbb1startWednesday, January 15, 2020
bbb1finishSaturday, January 25, 2020
bbb2startSaturday, February 15, 2020
bbb2finishThursday, February 20, 2020
bbb3startFriday, February 21, 2020
bbb3finishSunday, March 1, 2020
ccc1startSaturday, February 15, 2020
ccc1finishSunday, March 1, 2020
ccc2startThursday, March 5, 2020
ccc3finishTuesday, March 10, 2020
ccc3startSunday, March 15, 2020
ccc3finishWednesday, March 25, 2020
ddd1startSaturday, February 15, 2020
ddd1finishMonday, February 17, 2020
ddd2startThursday, February 20, 2020
ddd2finishTuesday, February 25, 2020
ddd3startSunday, March 1, 2020
ddd3finishWednesday, March 4, 2020





Super User
Super User

Please explain what to do in this scenario:




Super User
Super User

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.

Helpful resources

Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors