Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
klintala
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.

https://jtbhomes-my.sharepoint.com/:u:/p/klintala/EV2WmTx5R7JLj_72cf2EgS0BncSs2yrY6IQKVQZqa2rUXg?e=M...

 

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

 

 

 

 

3 REPLIES 3
lbendlin
Super User
Super User

Please explain what to do in this scenario:

 

lbendlin_0-1612231747973.png

 

lbendlin
Super User
Super User

Couple of questions:

How would you prevent a user from selecting nonsensical items like this:

 

lbendlin_0-1612150575562.png

How are you planning to compute the average in your earlier example?

 

lbendlin_1-1612150637001.png

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.

 

image.png

 

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

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.