DAX Help

[apurva11thakur]

Hi All,

I have two requirement were I have to find difference between Engineer to Customer response and Customer to Engineer response. Example : If the engineer has responded multiple times then we have to take the difference of the latest response against customer response and vice versa.

Please find the data table below :

TicketId	Name	ConversationTime	Diff	TicketId	Name	ConversationTime	Diff
ABC	Engineer	01-04-2020 17:36		ABC	Engineer	01-04-2020 17:36
ABC	Engineer	01-04-2020 17:43		ABC	Engineer	01-04-2020 17:43
ABC	Customer	01-04-2020 20:04	141	ABC	Customer	01-04-2020 20:04
ABC	Customer	01-04-2020 20:13		ABC	Customer	01-04-2020 20:13
ABC	Customer	01-04-2020 20:13		ABC	Customer	01-04-2020 20:13
ABC	Engineer	02-04-2020 13:19		ABC	Engineer	02-04-2020 13:19	1026
ABC	Customer	02-04-2020 16:42	203	ABC	Customer	02-04-2020 16:42
ABC	Engineer	02-04-2020 18:54		ABC	Engineer	02-04-2020 18:54	132
ABC	Customer	05-04-2020 15:45	4131	ABC	Customer	05-04-2020 15:45
ABC	Customer	05-04-2020 15:45		ABC	Customer	05-04-2020 15:45
ABC	Engineer	07-04-2020 10:49		ABC	Engineer	07-04-2020 10:49	2584

I have highlighted the required ones and non-highlighted ones are to be ignored.

Reply or ping me for more details and possible solution.

Thanks in advance

[Anonymous]

HI @apurva11thakur,

You can try to use the following measure formula to calculate between nearest different users to get the diff:

measure =
VAR currName =
    SELECTEDVALUE ( Table[Name] )
VAR currDate =
    MAX ( Table[ConversationTime] )
VAR _next =
    CALCULATE (
        MIN ( Table[ConversationTime] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Name] <> currName
                && [ConversationTime] > currDate
        ),
        VALUES ( Table[TicketId] )
    )
RETURN
    DATEDIFF ( currDate, _next, SECOND )

Regards,

Xiaoxin Sheng

[apurva11thakur]

Hi @Anonymous ,

I am not looking for the complete diference between the conversationtime column. I am looking for immediate difference between "Engineer" and "Customer" as well as "Customer" and "Engineer" column.

Hope i am clear with requirement.

Thanks,

Apurva

[Anonymous]

Hi @apurva11thakur,

My formula can use to find out the next records which not has the same name as the current name, it should suitable for your requirement.

In addition, did these fields exist in the same table? If this is a case, you only need to replicate it and replace its name field and date fields.

Regards,

Xiaoxin Sheng

[apurva11thakur]

Hi @Anonymous,

Yes i have TicketId, ConversationTime and Name in the same table. Other are custom columns which i have created to provide you my requirement.

Thanks & Regards,

Apurva