Xlookup convert to DAX

[saud968]

Hi,

this is excel formula =IFERROR(XLOOKUP(AJ26022,Validation!A:A,Validation!B:B,"No",0,1),"") to give you context for the DAX i made thinking as this formula - =IFERROR(XLOOKUP(NEW_VALUE,Validation[Name],Validation[Dispatch],"No",0,1),"")

Below is my DAX 

DispatchedNew =

VAR DN = SUPPORT__CW_OPS_VOLUME_PARTNER_CARE[NEW_VALUE]

VAR CombinedValues = CONCATENATEX(Validation, Validation[Name] & Validation[Dispatch], ", ")

VAR ResultColumn = "No"

RETURN

    IF(

        CONTAINSSTRING(CombinedValues, DN),

        "Yes",

        ResultColumn

    )

 
however, I am getting Yes  against Name "Sarah Basto" where are as it is not part of the list of people under the Table Validation 

NameDispatch

Aditya Gupte	Yes
Adrian Alvares	Yes
Akshay Dalvi	Yes
Amey Kamat	Yes
Aniket Chipkar	Yes
Arvind Vishwakarma	Yes
Ayraf Shaikh	Yes
Evan Kaufman	Yes
Frazer Fernandes	Yes
Hardeep Jaswal	Yes
Hena Soni	Yes
Hetal Bhojak	Yes
Jessica Holmes	Yes
Jim Whitecotton	Yes
John Patel	Yes
Josh Burns	Yes
Kelly Acosta	Yes
Kirit Ranpariya	Yes
Liam Bajic	Yes
Mary Hall	Yes
Mehjabeen Sayed	Yes
Nadim Sayed	Yes
Nicole Valencia	Yes
Nolan Pinto	Yes
Paul Collins	Yes
Priyanka Age	Yes
Rajeshkumar Patwa	Yes
Rolland Gomes	Yes
Ronak Waghela	Yes
Roopesh Gowda	Yes
Ruhi Shaikh	Yes
Sagar More	Yes
Salman Patel	Yes
Samson Fernandez	Yes
Sanket Bhayade	Yes
Saud Ansari	Yes
Sunil Dsouza	Yes
Terry Phillips	Yes
Vicki Edler	Yes
Vinay Singh	Yes
Viraj Dungrani	Yes
Yashwant Sawant	Yes
Abhay Parihar	Yes

@lbendlin @Ahmedx 

@Idrissshatila @amitchandak @parry2k @technolog 

 @Ritaf1983 

[Ahmedx]

pls try

[Ahmedx]

and try this

[saud968]

Getting this error for 102 file 

Getting this error for 103 file 

[Ahmedx]

you forgot the bracket

[Ahmedx]

incorrectly specified tables

[saud968]

It is now giving no to all 

VAR _Result = CALCULATE(MAX(Validation[Dispatch]),CROSSFILTER(Validation[Name],SUPPORT__CW_OPS_VOLUME_PARTNER_CARE[NEW_VALUE],Both))

RETURN

IF(ISBLANK(_Result),"No",_Result)

gives yes for everyone

[Ahmedx]

I don’t know what you're doing wrong, post the same file with the error so we can understand what’s wrong

[saud968]

https://drive.google.com/file/d/1_pW3R6ccJT7uMVistCHdHrqKC8XXk0-F/view?usp=drivesdk

@Ahmedx here is the file please check and help out. Thank you for all the work 

[Ahmedx]

try it
But what I noticed is that when the measure is changed, the calculation is not updated immediately; you need to close the file and reopen it. and it's very strange

[saud968]

Thank you for your help @Ahmedx  it is not working as you can see below Data Migration is not part of the validation list.

[Ahmedx]

try this

[saud968]

Thank you for the help I believe  I have identified the cause and reason for the panic was unnecessary.  So the concept was to check New_Value with Validate Table column Name. It was giving correct output but I failed to check debug if Sarah is showing up as Yes; What is the New_Value and the New_Value was part of the validation list hence Yes

There is another concern as you have the file the Month Unique Count column uses Rankx but I am not getting the ranking as 1, 2, 3 but 1, 1, for each entry. Should I post a new question or you can help with this article it. 

I appreciate all your help. 

[Ahmedx]

create a new request and send me a link to the topic.

When I have time, I'll help, just don't panic 🤝

[saud968]

Thank you so much, link for the new post - https://community.fabric.microsoft.com/t5/Desktop/Ranking-for-multiple-entries/m-p/3562408#M1173100

Also is there a guide where I can attach PBIX file directly. 

[Ahmedx · Accepted solution]

If this post helps, then please consider accepting it as the solution to help other members find it faster.

[Ahmedx]

what do you have on SUPPORT__CW_OPS_VOLUME_PARTNER_CARE[NEW_VALUE]

you can share the pibx file

[saud968]

I can share the excel file if you want. I appreciate all your help @Ahmedx 

[Ahmedx]

without a file it is difficult to understand the task create an example similar to your scenario without confidential data

[saud968]

please check the link below @Ahmedx 

https://drive.google.com/file/d/1tZPuqBd8oeT47BTaRz6W1vnpLiTc2jlM/view?usp=drivesdk

I forgot to create a relationship in the new file you can create a relationship with Column Name from Table1 and Case_Owner from Sheet 1

[Ahmedx]

not one here matches