Forum Discussion
Help with a LOOKUPVALUE
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 | [email protected] | 23-12-2021 11:00:00 |
| The Rock 2 | [email protected] | 22-12-2021 12:00:00 |
| The Rock 3 | [email protected] | 23-12-2021 10:58:12 |
| The Rock 4 | [email protected] | 02-12-2021 12:00:00 |
| The Rock 5 | [email protected] | 03-12-2021 13:00:00 |
| The Rock 6 | [email protected] | 12-12-2021 14:00:00 |
| The Rock 7 | [email protected] | 13-12-2021 14:00:00 |
| The Rock 8 | [email protected] | 14-12-2021 14:00:00 |
| The Rock 9 | [email protected] | 15-12-2021 14:00:00 |
| The Rock 10 | [email protected] | 16-12-2021 14:00:00 |
| The Rock 11 | [email protected] | 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 | [email protected] | La calle 123 |
| 124 | [email protected] | |
| 125 | [email protected] | La calle 125 |
| 126 | [email protected] | La calle 126 |
| 127 | [email protected] | |
| 128 | [email protected] | La calle 128 |
| 129 | [email protected] | La calle 129 |
| 130 | [email protected] | |
| 131 | [email protected] | La calle 131 |
| 132 | [email protected] | La calle 132 |
| 133 | [email protected] | 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
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.⚡
4 Replies
- parry2kSuper User
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.⚡
- parry2kSuper User
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.⚡
- Ivancito111Resolver I
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
- parry2kSuper User
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.⚡