Get first department join date for each client from a different table using DAX

Question

In my scenario, many clients moving between departments and the system is tracking this using a table with columns like: ClientID/ DepartmentID/ Start date.

What if I need to get a column with first department start-date for each client? I am trying to read this to a Client profile table from the above Department association table using DAX:

FIRSTDATE('ClientDepartment'[StartDate]) will give only a single date for all clients. I am looking to get something like:

For Example (see client, department movement and result needed tables:

This is for a tabular model and I am trying to add as a column (not as measure or calculated table).

I tried:

CALCULATE (
    FIRSTDATE ( ClientDepartments[StartDate] ),
    ClientDepartments[ClientID] = Clients[ClientID]
)

but throwing an error

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

Please show us the tables with the columns and some date which are relevant for your question and the error you are getting? — Aldert
What happens if you take the condition out (but leave the CALCULATE)? If you have a relationship, it should automatically filter other table. — Alexis Olson

Dan Dan · Accepted Answer · 2019-08-05T09:03:52

If the two tables have an active relationship, such that the Clients table is a dimension table for the ClientDepartments table, then you can simply use the following expression for the calculated column:

CALCULATE ( MIN ( 'ClientDepartments'[StartDate] ) )

If you don't have a relationship in place, use a variable:

VAR currentClient = 'Clients'[ClientID]
RETURN
    CALCULATE (
        MIN ( 'ClientDepartments'[StartDate] ) , 
        'ClientDepartments'[ClientID] = currentClient
    )

Get first department join date for each client from a different table using DAX

1 Answers