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 moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi All,
Below is the table containing multiple serial numbers, Ive taken just one serial number for example.
For each serial number the cases are open which is the Actualstart and close case is the actualend.
So If a case gets open within 48 hours or the Actualend, could you please tell me how to calculate the difference . You can see one such call below that was opened again within 48hours. Can you tell me how to get the "call duration table"
Thanks in advance
Serial Number | Call Type | SI Status | Activity | Actualstart | Actualend | Callduration |
ZCV-S777 | CM | green | Arrived on Site | 9/29/2020 8:00:00 AM | 9/29/2020 8:00:00 AM | |
ZCV-S777 | CM | green | Phone Repair | 6/6/2021 3:00:00 PM | 6/6/2021 3:16:52 PM | |
ZCV-S777 | CM | yellow | Phone Repair | 8/11/2021 6:33:17 PM | ||
ZCV-S777 | FIELD-ACT | green | Arrived on Site | 8/24/2021 12:00:00 PM | <48 hours | |
ZCV-S777 | CM | green | Phone Repair | 10/18/2021 8:30:00 AM | 10/18/2021 9:30:00 AM | |
ZCV-S777 | CM | yellow | Phone Repair | 9/24/2021 7:04:56 PM | 9/24/2021 8:42:06 PM |
Hi,
This calculated column formula works
=if(Data[Actualstart]-CALCULATE(MAX(Data[Actualend]),FILTER(data,Data[Serial Number]=EARLIER(Data[Serial Number])&&Data[Actualend]<EARLIER(Data[Actualstart])))<=2,"<48 hours",BLANK())
Hope this helps.
Hi,
Is it possible to create a measure instead of calculated column? since it involves lot of filters. so the front end report looks like the one above but the actual table has lot of columns.
A calculated column formula is best suited for a calculation like this. I am not sure of how to get your desired result via a measure.
If there are other tables in the model, how do we include that filters in the calculated column?
for instance I have another table (caseline) that has a column name 'statuscode' to be filtered as "Resolved".
similarly another table (activity) which has a column name 'code' which needs to be filtered as "CM"
Hi,
Could you share some data and show the expected result.
Hi, @axk180022,
based on how I interpret your question, I suggest this code for a calculated column:
Column =
VAR _serialNumber = 'Table'[Serial_Number]
VAR _rank = 'Table'[ranking]
VAR _currentEndDate = 'Table'[Actualend]
VAR _nextStartDate =
CALCULATE (
SELECTEDVALUE ( 'Table'[Actualstart] ),
FILTER (
ALL ( 'Table' ),
'Table'[ranking] = _rank + 1
&& 'Table'[Serial_Number] = _serialNumber
)
)
VAR _nextEndDate =
CALCULATE (
SELECTEDVALUE ( 'Table'[Actualend] ),
FILTER (
ALL ( 'Table' ),
'Table'[ranking] = _rank + 1
&& 'Table'[Serial_Number] = _serialNumber
)
)
VAR _isWithin48Hours =
IF (
_nextStartDate <= _currentEndDate + 2
&& NOT ( ISBLANK ( _nextStartDate ) ),
1,
0
)
VAR _minute =
IF (
_isWithin48Hours,
DATEDIFF ( 'Table'[Actualstart], _nextEndDate, MINUTE ),
DATEDIFF ( 'Table'[Actualstart], 'Table'[Actualend], MINUTE )
)
RETURN
_minute
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi,
The table does not have any column as ranking can you tell me what is it?
Thank you,
Akshatha
crikey, sorry, a calculated column like this:
Ranking =
VAR _currentSerial = 'Table'[SerialNumber]
RETURN
RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[SerialNumber] = _currentSerial ),
'Table'[ActualStart],
'Table'[ActualStart],
asc
)
Hi,
Is it possible to create a measure instead of calculated column? since it involves lot of filters. so the front end report looks like the one above but the actual table has lot of columns.
In theory it is possible, but how to write it will depend on your use case. Please provide a pbix-file which resembels your real report as much as possible. It should contain the model you are using, and quite a bit more of the data you provided in your original post
Upload to dropbox/onedrive/other and share the link
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
92 | |
91 | |
84 | |
80 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |