Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Get First and Last Date from Group

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
6 years ago
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])))

4 Replies

Anonymous
Not applicable
6 years ago
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
  Not applicable
  6 years ago
  The better question here might be then - How do you replicate a SQL GroupBY clause in DAX
  - Anonymous
    Not applicable
    6 years ago
    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]....