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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sam-L
Frequent Visitor

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

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?

 

Sam-L_0-1620228599169.png

Sam-L_1-1620228669921.png

Thank you 🙂

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1620785542205.png

 

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

v-robertq-msft_1-1620785542211.png

 

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

v-robertq-msft_2-1620785542324.png

 

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

v-robertq-msft_3-1620785542326.png

 

  1. 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:

v-robertq-msft_4-1620785542330.png

 

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.

View solution in original post

7 REPLIES 7
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1620785542205.png

 

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

v-robertq-msft_1-1620785542211.png

 

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

v-robertq-msft_2-1620785542324.png

 

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

v-robertq-msft_3-1620785542326.png

 

  1. 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:

v-robertq-msft_4-1620785542330.png

 

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
Frequent Visitor

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.

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”:

v-robertq-msft_0-1620715750077.png

 

Then the value can correctly display like this:

v-robertq-msft_1-1620715750081.png

 

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:

v-robertq-msft_2-1620715750087.png

 

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.

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_0-1620747262396.png

 

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
Community Support
Community Support

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:

v-robertq-msft_0-1620373600720.png

 

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
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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