Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

6 REPLIES 6
Anonymous
Not applicable

[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
Not applicable

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
Not applicable

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
Community Champion
Community Champion

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.
Mariusz Repczynski



Anonymous
Not applicable

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. 

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.
Mariusz Repczynski

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors