LOOKUPVALUE with multiple results

[IntaBruce]

Hi,

I am using LOOKUPVALUE to find the staff member associated with a particular record.  However, the table containing the information has multiple records for different dates.  In other words, one staff member could be associated with the record on the 1st September but another on th 5th .

So currently my code returns an error:

New Column:    Assigned Staff = LOOKUPVALUE(AssignedStaff[Staff Name],AssignedStaff[Record_UID],[UID])

   A table of multiple values was supplied where a single value was expected.

The AssignedStaff table has a CreatedDate (always populated) and an UpdatedDate (populated only if the record has been amended). 

The assignment record that I want would be the most recent.

I've been scratching my head over this for a while and can't work out how to formulate the DAX expession.

Any guidance much appreciated.  Thank you

[lbendlin]

I am using LOOKUPVALUE

Maybe don't?  Consider using TREATAS instead, and then a TOPN(1) on the results.

[IntaBruce]

That 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?

[IntaBruce · Accepted solution]

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 🙂