Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ads2003uk
Regular Visitor

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



1 ACCEPTED SOLUTION

@ads2003uk

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

barritown_0-1685106762595.png

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

View solution in original post

4 REPLIES 4
ads2003uk
Regular Visitor

@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







@ads2003uk

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

barritown_0-1685106762595.png

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

Thank you so much!!

barritown
Super User
Super User

Hi @ads2003uk,

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

barritown_0-1685091522019.png

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors