Forum Discussion
Returning the larger value when error is Multiple results found
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 |
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
4 Replies
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
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 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
Thank you so much!!
