Forum Discussion
How to create DAX formula using vlookupvalue with custom text for unmatched records
Hi, I'm able to bring corresponding a_name back to table 1 via vlookupvalue but any unmatched values are showing as blank. Is there a way to assign those unmatched values in a_name to 'Outside of London' in table 1?
Table 1 *-1 Table 2 *-1 Table 3
ID ID a_code a_code a_name
10001 10001 a1 a1 North London
10002 10002 a2 a2 East London
10003 10003 a3 a3 West London
10003 10004 a3
99999
Desired output for table 1 should look like this
ID a_name
10001 North London
10002 East London
10003 West London
10003 West London
99999 Outside of London
For context, table 1 is a bookings table where we allow repeating customers, table 2 contains all (distinct) customers in London, table 3 is London area lookup table. Customer ID 99999 in bookings table is from out of London so I like to flag those records as 'Outside of London'. The aim is to build 2 histograms in table 1, one showing distinct count of ID by a_name and another with count of ID by a_name.
Thanking you in advance.
Since you have two M:1 relationships, you can just use the RELATED function for your new column on Table1.
a_name = var thisAname = RELATED(Table3[a_name])
return IF(ISBLANK(thisAname), "Outside of London", thisAname)Regards,
Pat
4 Replies
- mahoneypat
Microsoft Employee
Since you have two M:1 relationships, you can just use the RELATED function for your new column on Table1.
a_name = var thisAname = RELATED(Table3[a_name])
return IF(ISBLANK(thisAname), "Outside of London", thisAname)Regards,
Pat
- BI_Jo
Resolver III
Hi,
Are you using the LOOKUPVALUE DAX function? If so, it looks like you can set the last optional parameter (<alternateResult>) to "Outside of London", this should replace the blanks you have:
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value> [, <search2_columnName>, <search2_value>]… [, <alternateResult>] )
Hope that helps
Jo
- amitchandak
Super User
Anonymous , if you want a column approch it is two step. From Table 3 to table 2, You can use related
New column in table 2
Name = related(Table3[a_name]) // Assumed they are joined
Table 2 to table 1- New column in table 1
New column =
var _1 = Maxx(filter(Table2, Table2[ID] = Table1[ID]),Table2[Name])
return
if(isblank(_1),"Outside of London" ,_1)
refer this video how copy data from one table to another: https://www.youtube.com/watch?v=czNHt7UXIe8
- Ashish_Mathur
Hi,
In Table2, write this calculated column formula
Area = LOOKUPVALUE(Table3[a_name],Table3[a_code],Table2[a_code])In Table1, write this calculated column formula
Calculated Column 1 = LOOKUPVALUE(Table2[Area],Table2[ID],Table1[ID],"Out of London")Hope this helps.
