Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 🙂
Solved! Go to 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):
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.
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):
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.
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:
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.
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!
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 :-)!
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.
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |