topic Re: Measure based on different filters in DAX Commands and Tips

Measure based on different filters

klintala — Mon, 01 Feb 2021 01:38:17 GMT

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=MU1LLL

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

Re: Measure based on different filters

lbendlin — Mon, 01 Feb 2021 03:38:59 GMT

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.

Re: Measure based on different filters

klintala — Tue, 02 Feb 2021 01:20:23 GMT

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.

Re: Measure based on different filters

lbendlin — Tue, 02 Feb 2021 02:09:17 GMT

Please explain what to do in this scenario: