Forum Discussion
Resolver ILOOKUPVALUE with multiple results
Sorry lbendlin, I have confused matters by misunderstanding the data myself. After consulting with the developers who support the source database they have confirmed that the dates are a red herring as there will only ever be one record but that it has to match not just the related work order record but also met be of the correct assignment type and also be the Primary record.
so, my final formula is:Assigned Staff = LOOKUPVALUE(AssignedStaff[Staff Name],AssignedStaff[Record_UID],[UID],AssignedStaff[StaffAssignmentType],1,AssignedStaff[IsPrimary],TRUE())Thank you for your time and efforts in helping me solve my problem. Kudos given 🙂
Super UserI am using LOOKUPVALUE
Maybe don't? Consider using TREATAS instead, and then a TOPN(1) on the results.
Resolver IThat might be a little beyond me at the moment, I've not come across TREATAS before. How might I use it in this scenario?
- lbendlin
Here's the documentation:
TREATAS function - DAX | Microsoft Learn
Pseudo code:
Assigned Staff = CONCATENATEX(TOPN(1,TREATAS({[UID]},AssignedStaff[Record_UID])),[Staff Name])
You'll want to validate that - and maybe use FILTER instead, or the data model if the tables are related.
If you can provide some sample data I can give a more solid answer.
- IntaBruce
Thanks, that isn't actually working but here is some more info and example data that might help.
The CaseWorkOrder table has a primary key of [UID] and contains various data about the case.
The AssignedStaff table has the following fields:
[UID] the Primary key
[Record_UID] which refers to the UID of CaseWorkOrder, though no relationship exists in the database
[StaffMember_UID] which relates to the StaffMember table
[CreatedDate]
[ModifiedDate]
[StaffAssignmentType] an Enum where a value of 1 means "Staff member is assigned to a case work order record." Other values indicate that the assignment record refers a different table.
I need a column in CaseWorkOrder that will give me the name of the currently assigned staff member, i.e. the one with the most recently created/modified entry in the AssignedStaff table.
Example:
CaseWorkOrder table
UID Assigned Staff* 97 S7 98 S2 99 S4 *new caclulated column
AssignedStaff table
UID Record_UID StaffMember_UID CreatedDate ModifiedDate StaffAssignmentType 01 99 S1 20240901 20240902 2 02
98 S2 20240901 1 03 99 S3 20240902 1 04 99 S4 20240901 20240903 1 05 99 S5 20240902 1 06 97 S6 20240905 1 07 97 S6 20240907 3 08 97 S7 20240906 1 Does that clafify the problem?
Thanks for you help- lbendlin
the one with the most recently created/modified entry in the AssignedStaff table.you lost me on this one. which date do you want to sort by, created or modified?