Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone, I hope you can help me... thanks in advance!!
The following scenario: We have objects. When these objects break, a service call is recorded. It's about calculating the average downtime.
There are the master data tables: service call & object.
A fact table FactSvcCall that provides the number of down days and the order.
And there is calendar table.
Sample Data:
Object
Obj_id | Obj_name |
SO1002438 | Abc Def |
SO2076722 | Ghi Jk |
Service call
Svc_id | Svc_name |
SV0014407 | A |
SV0018844 | B |
SV0020838 | C |
SV0024112 | sdf |
SV0028077 | gds |
SV0000572 | degf |
SV0000762 | hwsa |
SV0000741 | edgfr |
SV0001435 | hds |
SV0001870 | adsdf |
SV0002316 | hbdfg |
SV0002692 | dfrghh |
SV0003552 | asdggha |
SV0003827 | ghaadgf |
SV0003852 | gdaag |
SV070004171 | ghaadfgf |
FactSvcCall
Call_id | Obj_id | Svc_id | Date_call | DaysBetweenFailure | i_index |
1 | SO1002438 | SV0014407 | 16.12.2019 | 840 | 1 |
2 | SO1002438 | SV0018844 | 27.07.2020 | 224 | 2 |
3 | SO1002438 | SV0020838 | 23.11.2020 | 119 | 3 |
4 | SO1002438 | SV0024112 | 11.05.2021 | 169 | 4 |
5 | SO1002438 | SV0028077 | 24.11.2021 | 197 | 5 |
6 | SO2076722 | SV0000572 | 13.02.2021 | 345 | 1 |
7 | SO2076722 | SV0000762 | 22.03.2021 | 6 | 3 |
8 | SO2076722 | SV0000741 | 16.03.2021 | 31 | 2 |
9 | SO2076722 | SV0001435 | 05.07.2021 | 105 | 4 |
10 | SO2076722 | SV0001870 | 22.09.2021 | 79 | 5 |
11 | SO2076722 | SV0002316 | 06.12.2021 | 75 | 6 |
12 | SO2076722 | SV0002692 | 09.02.2022 | 65 | 7 |
13 | SO2076722 | SV0003552 | 08.06.2022 | 119 | 8 |
14 | SO2076722 | SV0003827 | 15.07.2022 | 37 | 9 |
15 | SO2076722 | SV0003852 | 22.07.2022 | 7 | 10 |
16 | SO2076722 | SV070004171 | 05.09.2022 | 45 | 11 |
In addition to the average, I also need the MIN and MAX over the course of time... also cumulatively.
The aim is to achieve the following overview:
The group is Svc_id and always calculate the sum of DaysBetweenFailure divided by the number starting from 1 date.
The data model:
Is there a way to calculate measures (Avg, Min, Max) so that you can then also flexibly filter based on the data model, i.e. get values from dimension tables in the normal way?
Solved! Go to Solution.
Hi, @denxx34
Please try formula like below:
cmulatively count =
CALCULATE (
DISTINCTCOUNT ( FactSvcCall[i_index] ),
FILTER (
ALLSELECTED ( FactSvcCall ),
FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
&& FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
)
)
cumulatively sum =
CALCULATE (
SUM ( FactSvcCall[DaysBetweenFailure] ),
FILTER (
ALLSELECTED ( FactSvcCall ),
FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
&& FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
)
)
Avg = [cumulatively sum]/[cmulatively count]
Max =
CALCULATE (
MAX ( FactSvcCall[DaysBetweenFailure] ),
FILTER (
ALLSELECTED ( FactSvcCall ),
FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
&& FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
)
)
Min =
CALCULATE (
MIN ( FactSvcCall[DaysBetweenFailure] ),
FILTER (
ALLSELECTED ( FactSvcCall ),
FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
&& FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
)
)
Best Regards,
Community Support Team _ Eason
Hi, @denxx34
Please try formula like below:
cmulatively count =
CALCULATE (
DISTINCTCOUNT ( FactSvcCall[i_index] ),
FILTER (
ALLSELECTED ( FactSvcCall ),
FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
&& FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
)
)
cumulatively sum =
CALCULATE (
SUM ( FactSvcCall[DaysBetweenFailure] ),
FILTER (
ALLSELECTED ( FactSvcCall ),
FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
&& FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
)
)
Avg = [cumulatively sum]/[cmulatively count]
Max =
CALCULATE (
MAX ( FactSvcCall[DaysBetweenFailure] ),
FILTER (
ALLSELECTED ( FactSvcCall ),
FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
&& FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
)
)
Min =
CALCULATE (
MIN ( FactSvcCall[DaysBetweenFailure] ),
FILTER (
ALLSELECTED ( FactSvcCall ),
FactSvcCall[Obj_id] = MAX ( Object[Obj_id] )
&& FactSvcCall[i_index] <= MAX ( FactSvcCall[i_index] )
)
)
Best Regards,
Community Support Team _ Eason
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |