Catch-all when no reference

[mterry]

I have a data table that has various info on it, all linked to location codes. I have a separate reference table that links those location codes to the common names of the locations. Sometimes there are instances where there is info on the data table that is linked to a code that is not truly a location, so not included on the reference table. What I would like to do is somehow include a label, i.e. 'misc', that is used for every instance when there is no location found, but I'm stuggling to do so. There isn't one or even several particular codes that I can put on the refernce table to address this as there are hundreds (maybe thousands) of potential codes that could show up on the data table..... 

Any assistance is appreciated 

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[mterry]

Okay here's a sample of specific data to help with my issue. The first table is the location reference table. The second table shows the actual data in our system, the third column being what I'm trying to figure out. The relationship between the two tables is the Code. In this sample, the charge codes 'QM456' and 'CA456' do not have an associated location, so what I'd like to have shown (as I've manually entered in the second table to show what I'd like) is 'Misc' for the location. The issue is that QM456 and CA456 are just made up examples, I have no idea what the Codes will be that aren't associated with locations, but I do know that Codes will come throug that don't have a location, so I'm looking for a way to have any code that doesn't exist in the reference table default to a location of 'Misc'. 

Code	Location
TX123	Austin, TX
CO123	Denver, CO
CA123	Sacramento, CA
FL123	Miami, FL

Code	Amount	Location
TX123	$                    100.00	Austin, TX
QM456	$                    100.00	Misc
CA123	$                    100.00	Sacramento, CA
CA456	$                    100.00	Misc

[Ashish_Mathur]

Hi,

Try this

=IF(ISBLANK(RELATED(Table1[Location])),BLANK(),RELATED(Table1[Location]))

Hope this helps.

[mterry]

Thank you both for the help, it worked

[Vvelarde · Accepted solution]

@mterry

Hi, You can use a calculated column like this

Location =
IF (
    RELATED ( Table2[Location] ) = BLANK ();
    "Misc";
    RELATED ( Table2[Location] )
)

Regards

Victor

Lima - Peru