Returning the larger value when error is Multiple results found

[ads2003uk]

Hi all,

I have two tables within PowerBI.
Table 1 SOA which contains UserID, TeamID, SDate & EDate.
Table 2 FLOW which contains UserID and TeamID --- I would like to return the EDate from SOA table.

As the SOA table can have the same UserID and TeamID appear mulitple (but have a different SDate & EDate) I expect and recieve the error "A table of multiple values was supplied where a single value was expected" .
(FLOWEDate = LOOKUPVALUE('SOA'[EDate],'SOA'[UserID],FLOW[UserID],'SOA'[TeamID],FLOW[TeamID]))

Is there away I can perform the lookup but return the most recent EDate if it is not 31/12/2999

SOA Table: 

TeamID	UserID	SDate	EDate
FIN_EM_BEVRG	FP99690	25/02/2020	02/03/2020
FIN_EM_BEVRG	FP99690	27/10/2021	30/10/2021
FIN_EM_BEVRG	FP99690	23/03/2023	31/12/2999
LCL_BEV_EMEA	FP99690	22/11/2019	31/12/2999
LCL_BEV_FR	FP99690	22/11/2019	31/12/2999
LCL_CCS_CENTRL	GP65842	20/07/2020	10/08/2020

[ads2003uk]

@barritown That worked - thanks and will be extremly useful for future PBI. (I will still accept the solution)

However the goal posts have now changed 😞
Same details as in SOA_Table but now in FLOW Table, I need to include an entry date.
This entry date will be inbetween the a range of SOA_Table SDATE & EDATE and the correct EDATE needs to be returned:

SOA Table:

USERID	TEAMID	SDATE	EDATE
FP9960	FIN_EM_BEVRG	25/02/2020	02/03/2020
FP9960	FIN_EM_BEVRG	27/10/2021	30/10/2021
FP9960	FIN_EM_BEVRG	23/03/2023	31/12/2999

FLOW Table:

USERID	TEAMID	ENTRY	EDATE to be returned
FP9960	FIN_EM_BEVRG	28/02/2020	02/03/2020
FP9960	FIN_EM_BEVRG	23/05/2023	31/12/2999
FP9960	FIN_EM_BEVRG	29/10/2021	30/10/2021

[barritown · Accepted solution]

@ads2003uk, 

Ok, with this goal post you can act like this:

If you have cases in your SOA table when the Entry date from the FLOW table fits multiple intervals, the minimal value of EDate will be chosen.

FLOWEDate = 
VAR CurrentTeamID = [TeamID]
VAR CurrentUserID = [UserID]
VAR CurrentEntry = [ENTRY]
RETURN CALCULATE ( MIN ( SOA[EDate] ), SOA, SOA[UserID] = CurrentUserID, SOA[TeamID] = CurrentTeamID, SOA[SDate] < CurrentEntry, SOA[EDate] > CurrentEntry )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[ads2003uk]

Thank you so much!!

[barritown]

Hi @ads2003uk,

If your tables are not connected, you could use a calculated column like this:

And the same in plain text:

FLOWEDate = 
VAR CurrentTeamID = [TeamID]
VAR CurrentUserID = [UserID]
RETURN CALCULATE ( MAX ( SOA[EDate] ), SOA, SOA[UserID] = CurrentUserID, SOA[TeamID] = CurrentTeamID )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian