Help with a LOOKUPVALUE

[Ivancito111]

Hello, sorry to bother you and thank you for taking the time to read my problem.
Also excuse my English, I am using DeepL Translator.

Well let's start. Today I was asked to use LOOKUPVALUE to search for a specific data, but if it doesn't find a data, search with another formula. For this I use the following data.

LeadsV2

SuuborigeneMailDate

The Rock 1	127@gmail.com	23-12-2021 11:00:00
The Rock 2	127@gmail.com	22-12-2021 12:00:00
The Rock 3	127@gmail.com	23-12-2021 10:58:12
The Rock 4	131@gmail.com	02-12-2021 12:00:00
The Rock 5	131@gmail.com	03-12-2021 13:00:00
The Rock 6	140@gmail.com	12-12-2021 14:00:00
The Rock 7	141@gmail.com	13-12-2021 14:00:00
The Rock 8	142@gmail.com	14-12-2021 14:00:00
The Rock 9	143@gmail.com	15-12-2021 14:00:00
The Rock 10	144@gmail.com	16-12-2021 14:00:00
The Rock 11	145@gmail.com	17-12-2021 14:00:00

modelov2

SuborigenWelcome GUID

123	La calle 123
125	La calle 125
126	La calle 126
128	La calle 128
129	La calle 129
131	La calle 131
132	La calle 132
133	La calle 133
134	La calle 134
135	La calle 135
136	La calle 136

TodoIntranet

lead_pilotcliente_mailOrigen Lead

123	123@gmail.com	La calle 123
124	124@gmail.com
125	125@gmail.com	La calle 125
126	126@gmail.com	La calle 126
127	127@gmail.com
128	128@gmail.com	La calle 128
129	129@gmail.com	La calle 129
130	130@gmail.com
131	131@gmail.com	La calle 131
132	132@gmail.com	La calle 132
133	133@gmail.com	La calle 133

In my attempt to do so, I logged in to get data from the table modelov2, and this is the code and the result

Origen Lead = 
var __suborigen = 
    LOOKUPVALUE( modelov2[Welcome GUID],
                'modelov2'[Suborigen],
                'TodoIntranet'[lead_pilot])

var __email = 
    LOOKUPVALUE('LeadsV2'[Suuborigen],
                'LeadsV2'[eMail],
                'TodoIntranet'[cliente_mail])


return __suborigen

The idea is that when the variable __suborigin is blank, it searches in the variable __email, but every time I try to test the variable __email I get the error that it has more than one data.

I understand the error, I checked in the LeadsV2 table and the data is repeated, but with different date, is there any way to take the first time, that is, the oldest date of the record.

As you can see there are 3 records with the same email, but I need the oldest one.
It should be noted that there are times when the difference is of hours, that is, they are the same day but one was made before the other.

Any kind of help would be important to me.

Thank you for everything.

Ivancito

[parry2k]

@Ivancito111 Sounds good. Glad it worked out. Cheers!!

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Ivancito111 or you can have small version of this:

Origen Lead = 
var __suborigen = 
    LOOKUPVALUE( modelov2[Welcome GUID],
                'modelov2'[Suborigen],
                'TodoIntranet'[lead_pilot])

return 
IF ( __suborigen == BLANK(),
   CALCULATE ( MAX ( 'LeadsV2'[Suuborigen] ), TOPN ( 1, Filter ( 'LeadsV2', 'LeadsV2'[eMail] = 'TodoIntranet'[cliente_mail] ), 'LeadsV2'[Date], DESC ) ) ,
   __suborigen 
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Ivancito111]

Hi, thank you very much for your reply, this was the only way that worked for me. That's right, as I needed the minor date first, I changed it from DESC to ASC.

Ivancito111

[parry2k]

@Ivancito111 try this

Origen Lead = 
var __suborigen = 
    LOOKUPVALUE( modelov2[Welcome GUID],
                'modelov2'[Suborigen],
                'TodoIntranet'[lead_pilot])

return 
IF ( __suborigen == BLANK(),
   VAR __table = CALCULATETABLE ( 'LeadsV2', TREATAS ( VALUES ('TodoIntranet'[cliente_mail] ), 'LeadsV2'[eMail] ) )
   VAR __maxDate = MAXX ( __table, [Date] )
   RETURN MAXX ( FILTER ( __table , [Date] = __maxDate), [Suuborigen] ),
   __suborigen 
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡