Forum Discussion
VLOOKUP in DAX
Hello folks,
I am kinda struggling past 2 days for a solution.
I have data that was working fine with LOOKUPVALUE function but now the data is updated and 1 variable has multiple rows as output. This is throwing an error since LOOKUPVALUE doesn't work with multiple values as a result. I need to figure out a query that looks for all the matching rows and gives them as an output.
For example:
Should give an output when written assuming lookupvalue function works -
LOOKUPVALUE(
COL1, ID=101)
Appreciate your feedback on this.
Not totally clear on how you are using the result, but this measure expression may work. This assumes the ID column is numeric. If not, wrap it in quotes like "101".
MatchingValues = var vMatches = CALCULATETABLE(VALUES(Table[Col1]), Table[ID]=101)
return CONCATENATEX(vMatches, Table[Col1], ", ")
Pat
3 Replies
- mahoneypat
Microsoft Employee
Not totally clear on how you are using the result, but this measure expression may work. This assumes the ID column is numeric. If not, wrap it in quotes like "101".
MatchingValues = var vMatches = CALCULATETABLE(VALUES(Table[Col1]), Table[ID]=101)
return CONCATENATEX(vMatches, Table[Col1], ", ")
Pat
Thank you mahoneypat I think this will open up a new door for my solution.
- Ashish_Mathur
Super User
Hi,
Since in Table1 there are no repetitions in Col 1, the LOOKUPVALUE() function in Table2 should work just fine.
