Min/ lookup formual help needed

[123rickfear]

Hi all,

first time posting in the BI forum, regular in Excel.

I am attempting to build a dashboard with regards to our work team email inbox and sent items.

I have created flows that save data to various excel sheets.

I have 2 tables in BI.

Table1 is data from the inbox.

From - Subject - CC - Received Time - PowerAppsId - Reply - Day

all our pulled from the sheet, except reply =if(left(subject,3)="Re:"),1,0) and day =day(received time)

Table2 is data from the sent items.

From - Subject - CC - Received Time - PowerAppsId - Original Subject - Sent Day - Received Day - Replied - Days to reply

from, subject, cc, RT, and PAid are from data sheet.

original subject =right(table1 subject,len(table2 subject)-3)

sent day = day(table2 received time)

received day = lookupvalue(table1 day,table1 subject, table2 original subject)

replied = if(isblank(table2 received day),1,0)

days to relpy = if(table2 replied =0, table2 received day - table2 sent day)

The outcome gives me a graph to show emails that we have replied to, and how many days it took to send the reply, which I out into a graph.

The issue I have is that there may be multiple replies to the same email, or an email chain, and I only want to count the first response.

example.

email comes in on the 20th subject new store opening.

22nd I reply subject is now re: new store opening

23rd they respond

2th I reply again subject is again re: new store opening.

my graph is getting messed up as it shows I took 2 days, and also 4 days to reply.

Sorry if I have confused anyone.

Any ideas?

 

[gazpage]

This is really hard to follow. I know people don't like walls of text, but one sentence per line is nearly as bad.

I think you would be best off with everything in one table, with an identifier for each email chain, and a column for sent or received. Something like this:

[TimeToFirstReply]:=
    CALCULATE ( 
        MIN ( Table[ReceivedDay] ),
        Table[SentOrReceived] = "Sent"
    )-
    CALCULATE ( 
        MIN ( Table[ReceivedDay] ),
        Table[SentOrReceived] = "Received"
    )

would give you the difference in days between the earliest sent message and the earliest received message.

This would be for the entire table, so not much use on it's own, but if you apply a filter for the email chain then it would just be for the emails related to that. E.g.

[AverageResponse]:=
AVERAGEX (
    Table[EmailChainID[,
    TimeToFirstReply]
)

should give you the average response time.