Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

1 ACCEPTED SOLUTION
mahoneypat
Employee
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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
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

BI_Jo
Resolver III
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

mahoneypat
Employee
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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.