New Column for earliest outlet visited

[Anonymous]

I have columns ID, Outlet & Date.

I would need to come up with a new column call First Transacted Outlet which shows the outlet with the earliest transacted date of the IDs.

ID	Outlet	Date transacted	First transacted outlet
A1	North	10-Dec-18	North
A1	South	24-Apr-20	North
B4	East	20-Aug-16	East
B4	North	9-Oct-20	East
C7	North	16-Jan-20	West
C7	South	20-Oct-19	West
C7	West	19-Jul-18	West

In another table I have the unique ID and Name of customer joining to this table, would I be able to include the new First Transacted Outlet into this table instead? The idea is to reduce the number of rows created per customer.

ID	Date transacted	First transacted outlet
A1	Harry	North
B4	Sam	East
C7	Catherine	West

Very inexperienced in in PBI so I will appreciate your help. Thank you.

[johnt75 · Accepted solution]

You could add a new column to the customer table like

First transacted outlet =
MINX (
    TOPN ( 1, RELATEDTABLE ( Sales ), Sales[Date transacted], ASC ),
    Sales[Outlet]
)

[Anonymous]

Thank you it works.

Just for my knowledge, are you also to provide me a way to include this row into the first table?

[johnt75]

I think the below should do it

First transacted outlet =
MINX (
    CALCULATETABLE (
        TOPN ( 1, Sales, Sales[Date transacted], ASC ),
        ALLEXCEPT ( Sales, Sales[ID] )
    ),
    Sales[Outlet]
)