Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
[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:
Best
Darek
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
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
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] ) )
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |