how to show blank for earlier dates and true data for the latest date

[marinalalves77]

Hello,

I have a spreadsheet that contains a list of emails and the dates when I sent an email to these people. In another database I have the date when those users finally joined my application after receiving one or more emails.

I have a table with the following behavior:

Table1

Email	Sent at
a@a.com	14/06/2020
a@a.com	20/11/2020
b@b.com	20/11/2020
c@c.com	20/11/2020

And another table with the following:

Table2

Email	JoinedOn
a@a.com	30/11/2020
b@b.com	30/11/2020
c@c.com	null

What I need to show is a table with all the times one user received an email, but I want only the latest email date to be shown withe the final date of join - the previous dates should appear as "ko", meaning that this first emails was not the responsible for making this user to join my application. Something like that:

Email	Sent at	Result of JoinedOn
a@a.com	14/06/2020	KO
a@a.com	20/11/2020	30/11/2020
b@b.com	20/11/2020	30/11/2020
c@c.com	20/11/2020	KO

Every query I come up with always shows me the result of JoinedOn in every row.

Do you have any ideas on how to write it properly?

Thank you so much in advance.

[amitchandak · Accepted solution]

@marinalalves77 , Create a new column in table 1

new column =
var _1 = maxx(filter(Table1,[Email] = earlier([Email])),[Sent at])
return
maxx(filter(Table2,Table2[Email] = Table1[Email] && Table1[Send at] =_1), Table2[Joined On])

[Anonymous]

Hi @marinalalves77 

Your final table is not what there should be. You are mixing different data types in one column. Namely, in "Result of JoinedOn" you are mixing dates and strings. This will not work. Of course, you could turn everything into text but if you try to perform any calculations on the column the DAX will be awkward and more complex than needed. Not to say it'll be slow.

If you really must do it like that in DAX, then this will work. But due to how NATURALOUTERJOIN works, the syntax is a bit awkward since one has to get rid of data lineage.

Final Table = // calculated table
var EmailsWithLatestDates =
    ADDCOLUMNS(
        DISTINCT( Table1[Email] ),
        "Sent at",
            var email = Table1[Email]
            // [Sent at] field must be of datetime type
            var LatestDate =
            MAXX(
                FILTER(
                    Table1,
                    Table1[Email] = email
                ),
                Table1[Sent at]
            )
            RETURN 
                LatestDate
    )
var EmailsWithJoinedOn =
    NATURALLEFTOUTERJOIN(
        SELECTCOLUMNS(
            EmailsWithLatestDates,
            "Email", Table1[Email] & "",
            "Sent at", [Sent at]
        ),
        SELECTCOLUMNS(
            Table2,
            "Email", Table2[Email] & "",
            "JoinedOn", FORMAT( Table2[JoinedOn], "dd/MM/YYYY" )
        )
    )
var AllTogether =
    SELECTCOLUMNS(
        NATURALLEFTOUTERJOIN(
            SELECTCOLUMNS(
                Table1,
                "Email", Table1[Email] & "",
                "Sent at", Table1[Sent at] + 0
            ),
            EmailsWithJoinedOn
        ),
        "Email",
            [Email],
        "Sent at",
            [Sent at],
        "Result of JoinedOn",
            If( [JoinedOn] = BLANK(), "KO", [JoinedOn] )
    )
return
    AllTogether

[marinalalves77]

Thank you for the suggestions.

The calculated column created by @amitchandak was simpler and it fits to my needs.

Anyway I didn't think about the fact that I'm mixing string and date in the same column, good point.