How to create DAX formula using vlookupvalue with custom text for unmatched records

[Anonymous]

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. 

[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.

[amitchandak]

@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

[BI_Jo]

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

[mahoneypat · Accepted solution]

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