Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |