Dax formula or suggestion needed for doing the equivalent of a vlookup, but on multiple columns

[tbobolz]

Excel 2010 PV 2.0
Hi All,

I am struggling with finding a way to lookup values in a related table, and if any of the values in the related table's column exist in any of 5 columns in the main database, then return a "Y" or something similar.

So I need the equivalent of a super charged Vlookup. By row, I wish to look at 5 columns, and if any of the values in the 5 columns exist in the related table's column, I would like it to return a conformation.


I have tried lookupvalue function but i keep recieving an error.

Thanks for any suggestions you can offer.

Terry

 

[scottsen]

Unlikely LOOKUPVALUE is going to work for you, since that is more of an "AND" than an "OR"... but before we go down that road... it might be easiest to add a calculated column in the lookup table that is "one of the 5 exist" or whatever, so you can just look at 1 column?

Otherwise the general pattern would be (looking at just 2 columns):
= IF (CALCULATE(COUNTROWS(LookupTable), FILTER(LookupTable, LookupTable[Field1] = "?" || LookupTable[Field2] = "?")) > 0, "Found Values", "No Values")

I'm not exactly sure what you are looking for so I put "?". Likely it is FactTable[SomeColumn] ?

 

[Fazza]

What about run 5 UPDATE queries? One for each field/table

UPDATE main_database_table M INNER JOIN related_table R ON M.field_name = R.field_name
SET M.ConfirmationField = 'Y'

 

[tbobolz]

Thank for both responses, I play around with them next week after month end fun and let you know how it goes.

 

[tbobolz]

Thanks Scott,

This really is a difficult one to explain. My first database named "Physician types" has five physician fields, (attending, admitting, PCP, referring and surgeon). In another database named "Physician Group Roster" I have physician assigned list in a cube to groups, so Dr. A, Dr. B, and Dr. C all work at Denver Physician Group. But I also have many physician groups and Drs. assigned within this database. So ulitmately I wish to select a physician group in a first database within a slicer that list all the groups names from the 2nd database. Then compare that physician group name to each of the 5 physician fields, on each row and return a "Y" in its own column in the 1st database. This will allow me to pivot financial data in the 1st database by selected physician group names.

Wow, hope that makes sense?

Thanks again for your help.

Terry

 

[scottsen]

You should probably mock up some data and share a workbook...