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

[Anonymous]

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:

Mathew - 12/03/ 2019

John - 09/ 07/ 2018

Raju - 08/ 08/ 2019  etc.

I tried:

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

but throwing an error

[Anonymous]

[First Date by Department and Client] = -- calculated table
ADDCOLUMNS(
	SUMMARIZE(
		ClientDepartments[DepartmentID],
		ClientDepartments[ClientID]
	),
	"Department Very First Date",
		CALCULATE( MIN ( ClientDepartments[StartDate] ) )
)

[First Date by Client] = -- calculated table
ADDCOLUMNS(
	SUMMARIZE(
		ClientDepartments[ClientID]
	),
	"Very First Date",
		CALCULATE( MIN ( ClientDepartments[StartDate] ) )
)

-- But you should try this:

[First Date] = CALCULATE( MIN ( ClientDepartments[StartDate] ) )

-- and slice your data by Clients[ClientID]

-- Your measure throws an error because FIRSTDATE returns
-- a table, not a value, and you should have used
-- CALCULATETABLE instead of CALCULATE.

You should always check if a function returns a value or a table:

https://dax.guide/firstdate/

Best

Darek

[Anonymous]

Thanks guys but sorry, I neither looking to add another calculated table nor as as measure but just trying to implemet this as a new column into the existing dimension table "Clients".

It is tabular model and no relationships created between any tables at the warehouse

[Anonymous]

Well, if you put this

 [First Date] = -- calculated column
    CALCULATE( 
        MIN ( ClientDepartments[StartDate] ),
        ALLEXCEPT (
            ClientDepartments, 
            ClientDepartments[DepartmentId],
            ClientDepartments[ClientID]
        )
    )

in your column, it'll calculate what you want. Depending on how big your table is, this might be very slow due to context transition. You could, however, re-write the above without using any context transition using FILTER.

Best

Darek

[Mariusz]

Hi @Anonymous 

Try the below.

CALCULATE( FIRSTDATE( ClientDepartments[StartDate] ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

That will read only one start-date for all clients. I need this against every client as shown in the question. So it need to check with CleintID. I even tried:

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

But got the 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. 

[Mariusz]

Hi @Anonymous 

From my understanding you have tried, as stated in you post.

FIRSTDATE( ClientDepartments[StartDate] ) 

Using Calculate will introduce context transition and if you have relationship on Client ID it would pick up first date for each client.

CALCULATE( FIRSTDATE( ClientDepartments[StartDate] ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.