cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Calls Received within 48 HOURS of case being closed

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"

 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 8/11/2021 9:30:00 PM ZCV-S777 FIELD-ACT green Arrived on Site 8/12/2021 12:00:00 PM 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

12 REPLIES 12
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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"

Super User

Hi,

Could you share some data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resident Rockstar

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.

Helper II

Hi,

The table does not have any column as ranking can you tell me what is it?

Thank you,

Akshatha

Resident Rockstar

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
)``````

Helper II

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.

Resident Rockstar

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

Helper II

I am not getting an option to insert the file. @sturlaws

Resident Rockstar

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors