Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
@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
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |