Get First and Last Date from Group

[Anonymous]

I have imported an Outlook Mail box and I want to flag the first and last email in a conversation.

I have started by normalising the subject by removing FW: and RE: so I can group the emails by subject.

I'm struggeling with the DAX to identify the first and last date in each group through.

I am currently trying the following:

StartDate = CALCULATE(MIN(Mail[DateTimeReceived]),filter(mail,Mail[Subject]))

But getting the error Cannot convert value from tect to Yes/No and I think thats my filter.

If I take the Filter out it complains about a circular dependency on mail[Startdate]

Any help would be appreciated.

Thanks.

[Anonymous]

As for the error:  Cannot convert value from text to Yes/No; 

You have to actually filter something. FILTER(TABLE,EXPRESSION) 

Table = mail <-- your table 

Expression = Mail[Subject] <-- your column

You might want to filter a specific subject text, i.e. FILTER(mail,Mail[Subject]="Subjectname")

[Anonymous]

The better question here might be then - How do you replicate a SQL GroupBY clause in DAX

[Anonymous]

I am now trying the following moving away from the Filter

StartDate = CALCULATE(MIN(Mail[DateTimeReceived]),Groupby(Mail,Mail[Subject]))

But now I get a circular dependency on mail[startdate]....

[Anonymous · Accepted solution]

Just incase anyone googles this in the future and needs the answer - here you go:

Earliest = Calculate(min(Mail[DateTimeReceived]),filter(mail,Mail[subject]=earliest(Mail[Subject])))

And

Latest = Calculate(max(Mail[DateTimeReceived]),filter(mail,Mail[subject]=earliest(Mail[Subject])))