topic Re: Get first department join date for each client from a different table using DAX in DAX Commands and Tips

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

Anonymous — Thu, 01 Aug 2019 09:00:56 GMT

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

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

Mariusz — Thu, 01 Aug 2019 09:13:35 GMT

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.

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

Anonymous — Thu, 01 Aug 2019 09:22:01 GMT

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.

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

Mariusz — Thu, 01 Aug 2019 09:31:01 GMT

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.

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

Anonymous — Thu, 01 Aug 2019 11:15:15 GMT

[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

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

Anonymous — Fri, 02 Aug 2019 03:33:43 GMT

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

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

Anonymous — Fri, 02 Aug 2019 17:29:23 GMT

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