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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
axk180022
Helper II
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"

 

Thanks in advance

 

 

 Serial   Number   Call  Type    SI Status        Activity       Actualstart       Actualend      Callduration   
       
ZCV-S777CMgreenArrived on Site9/29/2020 8:00:00 AM9/29/2020 8:00:00 AM 
ZCV-S777CMgreenPhone Repair6/6/2021 3:00:00 PM6/6/2021 3:16:52 PM 
ZCV-S777CMyellowPhone Repair8/11/2021 6:33:17 PM8/11/2021 9:30:00 PM 
ZCV-S777FIELD-ACTgreenArrived on Site8/12/2021 12:00:00 PM8/24/2021 12:00:00 PM<48 hours
ZCV-S777CMgreenPhone Repair10/18/2021 8:30:00 AM10/18/2021 9:30:00 AM 
ZCV-S777CMyellowPhone Repair9/24/2021 7:04:56 PM9/24/2021 8:42:06 PM 

 

@amitchandak @Ashish_Mathur 

 

12 REPLIES 12
Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

 

@Ashish_Mathur 

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
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sturlaws
Resident Rockstar
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.

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

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

Upload to dropbox/onedrive/other and share the link

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.