Average time displayed in a card from one fixed field across multiple fields

[Sam-L]

Hello,

I'm looking to create a card that displays an average time based on one field, 999 time, to first medical intervention, which is split between multiple columns (i.e. Intervention 1, Intervention 2, Intervention 3).

I didn't know if something like AVERAGE([999_Time]),AVERAGE([Intervention1],[Intervention2],[Intervention3]) would work?

Thank you 🙂

[v-robertq-msft · Accepted solution]

Hi, @Sam-L 

If you have 7 interventions in your table, then the DAX formula can’t be the best choice to help you to achieve this, you should try to achieve this using the Power query, just follow my steps(I have only 3 interventions in my table but the steps are the same):

1. Press on “Ctrl” to select the intervention columns and replace value like this:

2. Convert the intervention columns to “Decimal Number”:

3. Go to the “Add column” to add a minimum column like this:

4. Convert all the date columns back to the “Date/Time” type:

5. Then click “Apply and close”, go to the Power BI and change the calculated column like this:

Duration between 999 to first intervention =

DATEDIFF([999_Time],[Minimum],MINUTE)

You don’t have to change the measure, because the data can display as what you want:

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sam-L]

Hi @v-robertq-msft @amitchandak thanks for the info so far.

I think it might be best if I try and clear the scenario up a bit more.

So we have incidents which is shown as a new row in my Power BI data, each row is a different incident. Each incident has a 999_time.

- Each incident might have interventions which each different intervention is logged under Intervention1, Intervention2, Intervention3

- Intervention1 is not necessarily the first intervention that happens, it could be Intervention2,Intervention1,Intervention3 for example

What I need to work out is the average duration between the 999 time to the first intervention (which is the earliest intervention across multiple columns (Intervention2,Intervention1,Intervention3).

Example would be:

Incident 1

999 Time: 10/05/2021 13:10

Intervention1: 10/05/2021 14:15

Intervention2: 10/05/2021 13:50

Intervention3: null

Duration between 999 - first intervention = 40

Incident 2

999 Time: 10/05/2021 16:00

Intervention1: 10/05/2021 16:50

Intervention2: 10/05/2021 17:05

Intervention3: 10/05/2021 16:20

Duration between 999 - first intervention = 20

Total average duration between 999 time to first intervention between incident 1 and incident 2 = 30

I hope this makes sense and is clearer to understand.

[v-robertq-msft]

Hi, @Sam-L 

According to your description, I can clearly understand your requirement now, I think you can achieve this using a calculated column and a measure, you can try this:

1. Create a calculated column in the main table like this:

Duration between 999 to first intervention =

var _Inv1=IF([Intervention1]=BLANK(),DATE(9999,12,31),[Intervention1])

var _Inv2=IF([Intervention2]=BLANK(),DATE(9999,12,31),[Intervention2])

var _Inv3=IF([Intervention3]=BLANK(),DATE(9999,12,31),[Intervention3])

var _mintime=

IF(_Inv1<_Inv2,_Inv1,IF(_Inv2<_Inv3,_Inv2,_Inv3))

return

DATEDIFF([999_Time],_mintime,MINUTE)

And change the data type to the “Whole number”:

Then the value can correctly display like this:

Then create a measure like this:

Average =

AVERAGEX(ALL('Table'),[Duration between 999 to first intervention])

Then create a card chart to place this measure:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sam-L]

Hi @v-robertq-msft, just another quick question, I used three interventions as an example. However I actually have 7 interventions, what would the highlighted row look like after I have added my 7 interventions? That seems to be the line that I am confused with from your example. Thank you!

[Sam-L]

Thank you so much! That looks like it will be perfect for what I'm looking for. I'll give this a go and accept the solution if it works for me. Thanks :-)!

[v-robertq-msft]

Hi, @Sam-L 

According to your description and sample data, I can roughly understand your requirement, I think the key point you should keep in mind is to use the Format() function o convert to measure to the Date Time and filter the table to ignore null value when calculating the average value, you can try these measure:

Average 999 =

FORMAT(AVERAGE('Table'[999_Time]),"mm/dd/yyyy hh:nn:ss")

Average Intervention1 =

FORMAT(AVERAGEX(FILTER('Table',[Intervention1]<>BLANK()),[Intervention1]),"mm/dd/yyyy hh:nn:ss")

Average Intervention2 =

FORMAT(AVERAGEX(FILTER('Table',[Intervention2]<>BLANK()),[Intervention2]),"mm/dd/yyyy hh:nn:ss")

Average Intervention3 =

FORMAT(AVERAGEX(FILTER('Table',[Intervention3]<>BLANK()),[Intervention3]),"mm/dd/yyyy hh:nn:ss")

Then create four card charts to place the measures:

And you can get what you want.

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Sam-L , nor very clear.

Try like

averageX(union(all(Table[Intervention1]),all(Table[Intervention2])],all(Table[Intervention3])), [Intervention1])

OR

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.