topic Re: Returning the larger value when error is Multiple results found in DAX Commands and Tips

Returning the larger value when error is Multiple results found

ads2003uk — Thu, 25 May 2023 17:24:44 GMT

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

Re: Returning the larger value when error is Multiple results found

barritown — Fri, 26 May 2023 08:59:16 GMT

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

Re: Returning the larger value when error is Multiple results found

ads2003uk — Fri, 26 May 2023 12:48:50 GMT

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

Re: Returning the larger value when error is Multiple results found

barritown — Fri, 26 May 2023 13:16:26 GMT

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

Re: Returning the larger value when error is Multiple results found

ads2003uk — Fri, 26 May 2023 16:08:46 GMT

Thank you so much!!