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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ads2003uk
New Member

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
New Member

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors